MySQL - 删除级联的外键 - 是否有定义的执行顺序？

Question

我的 MySQL 有问题 - 多个表上的 CASCADE ON DELETE 规则。显然，“CASCADE ON DELETE”规则的执行顺序取决于它们的定义顺序。

但是这个执行顺序是明确定义的还是取决于 MySQL 版本？

这是我对三个表“A”、“B”、“C”的简单示例

create table `A` (`id` NUMERIC(10,0) NOT NULL)  ENGINE=INNODB;
create table `B` (`id` NUMERIC(10,0) NOT NULL, `a_id`  NUMERIC(10,0) NOT NULL) ENGINE=INNODB;
create table `C` (`id` NUMERIC(10,0) NOT NULL, `a_id`  NUMERIC(10,0) NOT NULL, `b_id` NUMERIC(10,0) NOT NULL) ENGINE=INNODB;

ALTER TABLE `A` ADD PRIMARY KEY (`id`);
ALTER TABLE `B` ADD PRIMARY KEY (`id`);
ALTER TABLE `C` ADD PRIMARY KEY (`id`);


ALTER TABLE `C` ADD CONSTRAINT `C1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE;
ALTER TABLE `B` ADD CONSTRAINT `B1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE;
ALTER TABLE `C` ADD CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`);

如果我插入以下数据:

INSERT INTO A (id) VALUES (1);
INSERT INTO B (id, a_id) VALUES (1,1);
INSERT INTO C (id, a_id, b_id) VALUES (1,1,1);

...并尝试删除表“A”中的唯一条目:

delete from A where id=1;

...我收到以下错误消息:

Cannot delete or update a parent row: a foreign key constraint fails (`C`, CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`))

但是如果我将外键约束的定义更改为:

ALTER TABLE `C` ADD CONSTRAINT `C1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE;
ALTER TABLE `C` ADD CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`);
ALTER TABLE `B` ADD CONSTRAINT `B1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`) ON DELETE CASCADE;

...一切都很好...

谢谢你的帮助

Answer 1

你的 ALTER TABLE table ADD CONSTRAINT foreign_key FOREIGN KEY 是一样的，所以它们不可能是错误的原因。

(...文本已删除)

---

无法解释这种奇怪的行为。可以建议解决方法:

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM A WHERE ID=1;
SET FOREIGN_KEY_CHECKS = 1;