Sometimes it could be a little bit strange if you try to drop a foreign key on a MySQL table. You get for example error messages like this one:
- Error on rename of ‘.\database\mytable’ to ‘.\database\#sql2-6ec-11′ (errno: 152)
- Error on rename of ‘.\database\#sql-6ec_13′ to ‘.\database\mytable’ (errno: 150)
Often times this is because adding foreign keys also adds an index key to the table (on top of the reference key).
To drop it you need to execute these two statements:
- ALTER TABLE <table> DROP FOREIGN KEY `<foreign key name>`;
- DROP INDEX <fk name or index name> ON <table>;
Here is another example (slight different):
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`;
