Menu
Foreign keys are causing me too many problems modifying an database structure to meet new requirements - I want to modify primary keys but it seems I can't when foreign keys reference the table in question (I think because MySQL drops the table and re-creates).
So while I work on the DB, I'd like to simply remove all the foreign keys and re-create them later. Is there a neat way to do so?
306
Dropping Foreign Key Constraints You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
Description. Once a foreign key has been created, you may find that you wish to drop the foreign key from the table. You can do this with the ALTER TABLE statement in SQL Server (Transact-SQL).
Run
SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') FROM information_schema.key_column_usage WHERE CONSTRAINT_SCHEMA = 'db_name' AND referenced_table_name IS NOT NULL; and run the output.
87
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
