I often forget how to modify a MySQL table to drop/add foreign key constraints. I usually think it’s:
ALTER TABLE `tablename` DROP CONSTRAINT`my_FK_1`
The proper syntax is “DROP FOREIGN KEY”. I typically use it like this:
ALTER TABLE `tablename` DROP FOREIGN KEY `my_FK_1`, ADD CONSTRAINT `my_new_FK` FOREIGN KEY (`column_in_this_table`) REFERENCES `other_table` (`other_table_pk_column`) ON DELETE SET NULL
via MySQL :: MySQL 5.1 Reference Manual :: 12.1.7 ALTER TABLE Syntax.
I continue to learn from your weblog, Scott. Any reason why the latter syntax is preferable to the first? Does the first throw an error?
By Lawrence Krubner on Mar 12, 2010
Yes, the first isn’t valid and throws an error. It’s odd that to create a foreign key, the syntax is “…add constraint…” but to drop it, the syntax is “…drop foreign key…”
By Scott Meves on Mar 12, 2010