MySQL 删除MYSQL数据库中的所有外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13733281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DROP all foreign keys in MYSQL database
提问by Mr. Boy
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).
外键导致我在修改数据库结构以满足新要求时遇到太多问题 - 我想修改主键，但当外键引用有问题的表时似乎我不能（我认为是因为 MySQL 删除了该表并重新创建）。
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?
因此，当我在数据库上工作时，我想简单地删除所有外键并稍后重新创建它们。有没有一种巧妙的方法来做到这一点？
回答by Zoozy
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.
并运行输出。
回答by Mike Brant
You can simply issue the following command before any Alter Table statements you are going to make:
您可以在要执行的任何更改表语句之前简单地发出以下命令：
SET foreign_key_checks = 0;
This will turn off foreign key constraint checks for your database connection. You can then make your changes without needing to worry about constraints.
这将关闭数据库连接的外键约束检查。然后，您可以进行更改而无需担心约束。
After you are done, don't forget to issue:
完成后，不要忘记发出：
SET foreign_key_checks = 1;
To turn them back on.
重新打开它们。
Note that this will still not allow you to create a new foreign key constraint that would fail because the column data types don't match.
请注意，这仍然不允许您创建会因列数据类型不匹配而失败的新外键约束。
回答by Grégor González
Another version of Zoozy code, here you can select only a table:
Zoozy 代码的另一个版本，这里只能选择一个表：
SELECT concat('ALTER TABLE ', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA = 'YOUR DB HERE'
AND TABLE_NAME='YOUR TABLE HERE'
AND REFERENCED_TABLE_NAME IS NOT NULL;
Also with a procedure:
还有一个程序：
DROP PROCEDURE IF EXISTS dropForeignKeysFromTable;
delimiter ///
create procedure dropForeignKeysFromTable(IN param_table_schema varchar(255), IN param_table_name varchar(255))
begin
declare done int default FALSE;
declare dropCommand varchar(255);
declare dropCur cursor for
select concat('alter table ',table_schema,'.',table_name,' DROP FOREIGN KEY ',constraint_name, ';')
from information_schema.table_constraints
where constraint_type='FOREIGN KEY'
and table_name = param_table_name
and table_schema = param_table_schema;
declare continue handler for not found set done = true;
open dropCur;
read_loop: loop
fetch dropCur into dropCommand;
if done then
leave read_loop;
end if;
set @sdropCommand = dropCommand;
prepare dropClientUpdateKeyStmt from @sdropCommand;
execute dropClientUpdateKeyStmt;
deallocate prepare dropClientUpdateKeyStmt;
end loop;
close dropCur;
end///
回答by mpen
If you want to do the same table across multiple databases, don't forget to put CONSTRAINT_SCHEMAin the output:
如果你想跨多个数据库做同一个表，不要忘记CONSTRAINT_SCHEMA输入输出：
SELECT concat('ALTER TABLE ', CONSTRAINT_SCHEMA,'.',TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';')
FROM information_schema.key_column_usage
WHERE CONSTRAINT_SCHEMA like 'your_db_prefix_%'
AND TABLE_NAME='your_table'
AND REFERENCED_TABLE_NAME IS NOT NULL;
