Created
March 30, 2015 15:21
-
-
Save jezhalford/51a6d0b6d9f563aff3c5 to your computer and use it in GitHub Desktop.
Get DROP and CREATE statements for all foreign key constraints on a given table.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- source http://stackoverflow.com/a/13764961/86780 | |
SELECT `DROP`,`CREATE` FROM ( | |
SELECT | |
CONCAT("ALTER TABLE `", `K`.`TABLE_NAME`, "` DROP FOREIGN KEY `", `K`.`CONSTRAINT_NAME`, "`;") "DROP", | |
CONCAT("ALTER TABLE `", | |
`K`.`TABLE_NAME`, | |
"` ADD CONSTRAINT ", | |
"`fk_", | |
`K`.`TABLE_NAME`, | |
"_", | |
`K`.`REFERENCED_TABLE_NAME`, | |
"1", | |
"` FOREIGN KEY (`", | |
`K`.`COLUMN_NAME`, | |
"`) REFERENCES ", | |
"`", | |
`K`.`REFERENCED_TABLE_SCHEMA`, | |
"`.`", | |
`K`.`REFERENCED_TABLE_NAME`, | |
"` (`", | |
`K`.`REFERENCED_COLUMN_NAME`, | |
"`) ON DELETE ", | |
`C`.`DELETE_RULE`, | |
" ON UPDATE ", | |
`C`.`UPDATE_RULE`, | |
";") "CREATE" | |
FROM `information_schema`.`KEY_COLUMN_USAGE` `K` | |
LEFT JOIN `information_schema`.`REFERENTIAL_CONSTRAINTS` `C` USING (`CONSTRAINT_NAME`) | |
WHERE `K`.`REFERENCED_TABLE_SCHEMA` = "your_schema" | |
AND `K`.`REFERENCED_TABLE_NAME` = "your_table") AS DropCreateConstraints |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment