带有联接和外键约束的SQL Delete

Question

使用10.0.31-MariaDB-1~jessie。

我有两个表：provider_contact和provider_contact_x_role，其中provider_contact_x_role表中的contact_id列有一个对provider_contact表中id列的外键引用。

当我运行以下查询时，结果是成功的：

DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.is_test_contact = 0;

当我运行下一个查询时：

DELETE cr, c
FROM provider_contact_x_role AS cr
INNER JOIN provider_contact AS c
ON cr.contact_id = c.id
WHERE c.email_address <> '[e-mail]';

结果是以下错误：

Cannot delete or update a parent row: a foreign key constraint fails 
(`ins_db`.`provider_contact_x_role`, CONSTRAINT 
`FK_contact_id--provider_contact.id` FOREIGN KEY (`contact_id`) 
REFERENCES `provider_contact` (`id`))

请注意，这两个查询之间的唯一区别是WHERE条件。

问题：

1( SQL中是否有任何东西可以确保在执行这些查询时，首先删除provider_contact_x_role中的行？

2( 知道这两个查询之间的区别吗(为什么第一个查询成功，第二个查询失败(？

Answer 1

从MySQL文档中提取：

如果使用涉及InnoDB表的多表DELETE语句对于存在外键约束的，MySQL优化器可能以与表不同的顺序处理表父母/子女关系。在这种情况下，语句将失败并滚动返回相反，您应该从单个表中删除，并依赖InnoDB提供的ON DELETE功能可导致其他表以进行相应的修改。

如果您使用EXPLAIN，您会看到它更喜欢首先通过主表进行筛选(和删除(，因为它需要它来执行JOIN。

如果您使用INNER JOIN，您可以尝试STRAIGHT_JOIN，它与INNER JOIN相同，只是左表总是在右表之前读取，并按照您想要实现的删除顺序放置表。

作为级联的替代方案，您可以使用SET FOREIGN_KEY_CHECKS=0;禁用delete语句中的外键。

Answer 2

您是否可以尝试首先删除provider_contact表中的记录，而不是provider_contact_x_role，因为它从contact_id外键强制执行引用完整性。

DELETE c, cr
FROM provider_contact AS c
INNER JOIN provider_contact_x_role AS cr
ON c.id = cr.contact_id
WHERE c.email_address <> '[e-mail]';