Drop a Foreign Key in MySQL

in

​Fiddling with the removal of a foreign key in MySQL can drive an engineer mad.  In order to remove the key you must use the name of the constraint in the drop statement - not the name of the index.

The show command lists out the DDL for the table, including the created foreign keys:​

mysql> show create table t1;

...
  CONSTRAINT `MY_TABLE_FK05` FOREIGN KEY (`FLIGHT_ID`,
`USER_ID`) REFERENCES `MY_OTHER_TABLE` (`FLIGHT_ID`,
`USER_ID`)

You can then issue the drop command with the name of the constraint (yeah, not really intuitive)

mysql> alter table t1 drop foreign key MY_TABLE_FK05;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0