What does mysql error 1025 (HY000): Error on renam

Question

I tried this in mysql:

mysql> alter table region drop column country_id;

And got this:

ERROR 1025 (HY000): Error on rename of './product/#sql-14ae_81' to
'./product/region' (errno: 150)

Any ideas? Foreign key stuff?

Answer 1

In my case, I was using MySQL workbench and I faced the same issue while dropping one of my columns in a table. I could not find the name of the foreign key. I followed the following steps to resolve the issue:

1. Rt. click on your schema and select 'schema inspector'. This gives you various tables, columns, indexes, ect.

2. Go to the tab named 'Indexes' and search the name of the column under the column named 'Column'. Once found check the name of the table for this record under the column name 'Table'. If it matches the name of the table you want, then note down the name of the foreign key from the column named 'Name'.

3. Now execute the query : ALTER table tableNamexx DROP KEY foreignKeyName;

4. Now you can execute the drop statement which shall execute successfully.

Answer 2

I know, this is an old post, but it's the first hit on everyone's favorite search engine if you are looking for error 1025.

However, there is an easy "hack" for fixing this issue:

Before you execute your command(s) you first have to disable the foreign key constraints check using this command:

SET FOREIGN_KEY_CHECKS = 0;

Then you are able to execute your command(s).

After you are done, don't forget to enable the foreign key constraints check again, using this command:

SET FOREIGN_KEY_CHECKS = 1;

Good luck with your endeavor.

Answer 3

Take a look in error file for your mysql database. According to Bug #26305 my sql do not give you the cause. This bug exists since MySQL 4.1 ;-)

Answer 4

You can get also get this error trying to drop a non-existing foreign key. So when dropping foreign keys, always make sure they actually exist.

If the foreign key does exist, and you are still getting this error try the following:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

// Drop the foreign key here!

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This always does the trick for me :)

Answer 5

Simply run the alter table query using 'KEY' instead of 'FOREIGN KEY' in the drop statement. I hope it will help to solve the issue, and will drop the foreign key constraint and you can change the table columns and drop the table.

ALTER TABLE slide_image_sub DROP  KEY  FK_slide_image_sub;

here in DROP KEY instead of DROP FOREIGN KEY,

hope it will help.

Thanks

Answer 6

Doing

SET FOREIGN_KEY_CHECKS=0;

before the Operation can also do the trick.