unable to drop the foreign key

Question

I would like to drop the foreign key in my table but been into this error message

mysql> alter table customers drop foreign key customerid;
ERROR 1025 (HY000): Error on rename of '.\products\customers' to '.\products\#sql2-7ec-a3' (errno: 152)
mysql>

Answer 1

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key.

When I tried

mysql> ALTER TABLE mytable DROP PRIMARY KEY;

I got error as

ERROR 1025 (HY000): Error on rename of '.\database\#sql-454_3' to '.\database\mytable' (errno: 150).

I solved it using:

mysql> ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY (column1,column2,column3);

Some links that will help you.

link 1

link 2 [look for Posted by Alex Blume on November 7 2008 5:09pm & Posted by Hector Delgadillo on January 21 2011 4:57am]

Answer 2

To avoid getting this error while trying to drop a foreign key, use the constraint name rather than the column name of the foreign key

Answer 3

It looks like a bug in the error messaging of MySQL. (http://bugs.mysql.com/bug.php?id=10333)

Use SHOW CREATE TABLE table_name to see the actual name of the foreign key. It looks like it might be mysql query browser problem when generating the query with wrong spelling of the foreign key name.

Answer 4

The solution described here by Chris White worked for me.

The root problem is that MySQL creates both an index and a foreign key. Both must be removed (the foreign key first contrary to what Chris said).

1. show create table table_name;

   SHOW CREATE TABLE `table_name`:
   
   | table_name | CREATE TABLE `table_name` (
     `id` int(20) unsigned NOT NULL auto_increment,
     `key_column` smallint(5) unsigned default '1',
     KEY `column_tablein_26440ee6` (`key_column`),  <--- shows key name
     CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES <--- shows foreign key constraint name
   `second_table` (`id`) ON DELETE SET NULL
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
   

2. Delete the foreign key constraint:

   ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;
   

3. Delete the key

   ALTER TABLE table_name DROP KEY `column_tablein_26440ee6`;
   

That did it for me.

Answer 5

You should try with the foreign key name as Fahim Parkar suggested. Actually that does not work always either.

In my case I used the

FOREIGN KEY `fk`(`col1`) REFERENCES `table2`(`col1`)

code to add the fk by creation.

The problem with this code that it is not valid and should throw some kind of syntax error, but still it added a foreign key with a random name.

When I added the fk with the right syntax:

CONSTRAINT `fk` FOREIGN KEY (`col1`) REFERENCES `table2`(`col1`)

the following code dropped it properly:

ALTER TABLE `table1` DROP FOREIGN KEY `fk`

So this kind of error can happen too if you try to remove a foreign key with an invalid name. It is important to view the table properties with

SHOW CREATE TABLE `table1`

and check the foreign key names if you get this kind of errors.