Query to get drop and create foreign keys pointing to a table

When making DB scripts that massively manipulate big tables in MySQL sometimes you want do disable all foreign keys pointing to a particular table, make some operations and then enable the foreign keys again.

You can query the mysql information schema to get the metainformation of the foreign keys and generate the appropiate statements.

Just change the $db-schema-name and $table-name in this query and you can use the drop_cmd column that contains all the drop foreign keys statements and the create_cmd column that contains the add constraint statements to enable them again

SELECT r.CONSTRAINT_SCHEMA db, r.REFERENCED_TABLE_NAME referenced_table_name
, r.CONSTRAINT_NAME, r.TABLE_NAME
, c.COLUMN_NAME, c.REFERENCED_COLUMN_NAME
, r.UPDATE_RULE, r.DELETE_RULE
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME, ' drop foreign key ',r.CONSTRAINT_NAME,';') drop_cmd 
, CONCAT ('alter table `',r.CONSTRAINT_SCHEMA,'`.', r.TABLE_NAME
, ' add constraint ', r.CONSTRAINT_NAME
,' foreign key (',c.COLUMN_NAME,')'
,' references ', r.REFERENCED_TABLE_NAME, '(',c.REFERENCED_COLUMN_NAME,')'
,' on delete ', r.delete_rule
,' on update ', r.update_rule
,';') create_cmd 
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
, INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE r.CONSTRAINT_SCHEMA = '$db-schema-name'
AND r.REFERENCED_TABLE_NAME = '$table-name'
AND c.REFERENCED_TABLE_SCHEMA = r.CONSTRAINT_SCHEMA
AND c.REFERENCED_TABLE_NAME = r.REFERENCED_TABLE_NAME
AND c.CONSTRAINT_NAME = r.CONSTRAINT_NAME
ORDER BY r.CONSTRAINT_NAME

Hope this will help you!