从具有外部约束的多个表中删除 [英] Deleting from multiple tables with foreign constraints

问题描述

我正在尝试从多个表中删除.这是我的桌子的样子

    A_has_B ---- B ---- C_has_B
(many to many)        (many to many)

鉴于给定B中的记录ID，我试图从A_has_B，B和C_has_B中删除所有行.我将MySQL与innodb存储引擎配合使用，并为A_has_B和C_has_B定义了引用B中ID的外键.

我试图像这样执行删除操作:

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);

问题是当我执行查询时，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)

我该如何解决这个问题?

解决方案

最简单的方法是分别从每个表中删除:

-- 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还允许您在一个语句中从多个表中删除.但是无法控制删除的顺序.从手册:

如果您使用涉及具有外键约束的InnoDB表的多表DELETE语句，则MySQL优化器可能以与其父/子关系不同的顺序处理表.在这种情况下，该语句将失败并回滚.相反，您应该从单个表中删除，并依靠InnoDB提供的ON DELETE功能来相应地修改其他表.

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.

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:

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?

解决方案

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:

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.

这篇关于从具有外部约束的多个表中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！