Re: Deleting Foreign Key

[Chris White]

Mungbeans wrote:
>  ALTER TABLE `mytable` DROP FOREIGN KEY `keyname` 
> #1025 - Error on rename of '.\database\mytable' to '.\database\#sql2-6ec-11'
> (errno: 152)
>
>  ALTER TABLE `mytable` DROP INDEX `keyname`
> #1025 - Error on rename of '.\database\#sql-6ec_13' to '.\database\mytable'
> (errno: 150

Oh these are fun.  Often times this is because adding foreign keys also 
adds an index key to the table (on top of the reference key).  Why I 
don't know, but I'm assuming it adds it for optimization since it knows 
you'll be hitting that column (otherwise you wouldn't have created the 
reference in the first place).  Moving along... what I do first is 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 `key_column` (`key_column`),
  CONSTRAINT `table_name_ibfk_1` FOREIGN KEY (`key_column`) REFERENCES 
`second_table` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

So, first you remove the key:

ALTER TABLE table_name DROP KEY `key_column`;

then the foreign key:

ALTER TABLE table_name DROP FOREIGN KEY `table_name_ibfk_1`;

Then that should do it.  Also note that might also fail if you have, 
say, another table foreign key referencing to `key_column`.

Hope this helps, I also wrote this at about 8AM in the morning while 
slowly taking in caffeine, so be warned :).