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>

Pilliwinks

This may help you to understand #160733 – Lallation

@Lallation This link describes another error - (errno: 150) – Convex

Try this. Create new table (without foreign key constraint). copy the data to new table (using syntax INSERT INTO new_table SELECT * FROM old_table) Drop the old table – Lallation

Have you checked @Maksym Polshcha's answer? You have to use the foreign key's name not the column name. (it might be something like fk_customerid) – Lallation

check my answer and let me know if you still have problem... – Cubby

I also got this error when trying to use DROP FOREIGN KEY on a key that wasn't actually foreign - changing to DROP KEY fixed my problem. – Phenolphthalein

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]

Cubby

Answer 2

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.

Dedifferentiation

+1 Want to add that there's many questions (perhaps duplicates) of this, and the answer by @Dedifferentiation is the one that worked for me. – Tundra

i dont have constraint section in show create table 'name' but when i do desc 'name' i can see field to have foreign key constraint – Nagging

This is a perfect example which shows how to deal with foreign key drop. Many many thanks to @bbframe .it worked for me. – Sumptuary

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.

Ruderal

It helps me to know that my constraint was created as INDEX, not FOREIGN KEY. Then I used "DROP INDEX" clause. Thank you. – Acolyte

Answer 4

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

Chambless

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.

Orsini