从具有外部约束的多个表中删除

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。

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?

我该怎么解决这个问题呢?

4 个解决方案

#1

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表的多表删除语句，其中包含外键约束，那么MySQL优化器可能会按照与其父/子关系不同的顺序处理表。在这种情况下，语句失败并回滚。相反，您应该从单个表中删除，并依赖InnoDB提供的on delete功能，以便相应地修改其他表。

4 个解决方案

#1

更多相关文章

随机推荐