MySQL 从具有外部约束的多个表中删除
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6881905/
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
Deleting from multiple tables with foreign constraints
提问by F21
I am trying to delete from multiple tables. Here's what my tables look like
我正在尝试从多个表中删除。这是我的桌子的样子
A_has_B ---- B ---- C_has_B
(many to many) (many to many)
I am trying to delete all rows from A_has_B, B and C_has_B given the ID of a record in B. I am using MySQL with the innodb storage engine with foreign keys defined for A_has_B and C_has_B referencing the IDs in B.
我正在尝试从 A_has_B、B 和 C_has_B 中删除所有行，给出 B 中记录的 ID。我将 MySQL 与 innodb 存储引擎一起使用，外键定义为 A_has_B 和 C_has_B 引用 B 中的 ID。
I am trying to perform my delete like so:
我正在尝试像这样执行删除：
DELETE A_has_B.*, C_has_B.*, B.*
FROM
A
join
B
on (B.B_id = A.B_id)
join
C
on (C.B_id = B.B_id)
where B.B_id IN(1,2, 4);
The problem is that when I execute the query, mysql complains:
问题是当我执行查询时，mysql 抱怨：
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`db`.`C`, CONSTRAINT `fk_C` FOREIGN KEY (`B_id`) REFERENCES `B` (`B_id`) ON DELETE NO ACTION ON UPDATE NO)
How can I go about fixing this?
我该如何解决这个问题？
回答by Markus Jarderot
The simplest way would be to delete from each table individually:
最简单的方法是分别从每个表中删除：
-- Remove all connections from A which reference
-- the B-rows you want to remove
DELETE FROM A_has_B
WHERE B_id IN (1,2,4);
-- Remove all connections from C which reference
-- the B-rows you want to remove
DELETE FROM C_has_B
WHERE B_id IN (1,2,4);
-- Finally remove the B-rows
DELETE FROM B
WHERE B_id IN (1,2,4);
MySQL also allows you to delete from multiple tables in one statement. But there is no way to control the order of the deletions. From the manual:
MySQL 还允许您在一个语句中从多个表中删除。但是没有办法控制删除的顺序。从手册：
If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.
如果您使用涉及具有外键约束的 InnoDB 表的多表 DELETE 语句，则 MySQL 优化器可能会以不同于其父/子关系的顺序处理表。在这种情况下，语句失败并回滚。相反，您应该从单个表中删除并依赖 InnoDB 提供的 ON DELETE 功能来相应地修改其他表。
回答by Andrey
Actually, in MySQL, you can turn off checks for foreign key constraints
实际上，在 MySQL 中，您可以关闭对外键约束的检查
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
--your SQL statements
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
The statement on the first line forces MySQL server to turn off foreign key checks and the last line turns them back on(very important). Two things to keep in mind:
第一行的语句强制 MySQL 服务器关闭外键检查，最后一行将它们重新打开（非常重要）。要记住两件事：
- It's fairly dangerous to turn off checks for constraints and isn't something that should be done in, say, a production DB... the safest way is to use separate statements.
- Always turn the constraint checks back on
- 关闭约束检查是相当危险的，而不应该在生产数据库中完成……最安全的方法是使用单独的语句。
- 始终重新打开约束检查
回答by Emanuele Scozzafava
You can specify the "delete cascade" on the foreign key. When you delete the parent row, the mysql engine will delete the records in the related child tables
您可以在外键上指定“删除级联”。删除父行时，mysql引擎会删除相关子表中的记录
回答by Tom Squires
Do the deletes in separate statments and it will work.
在单独的语句中进行删除，它会起作用。
