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

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 2

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 3

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]