unable to drop the foreign key

solomon picture solomon · May 17, 2012 · Viewed 28.9k times · Source

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

bbrame picture bbrame · Feb 4, 2014

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.