MySQL - 删除级联上的外键 - 是否有定义的执行顺序? [英] MySQL - Foreign key on delete cascade - Is there a defined execution order?
问题描述
我在使用 MySQL 时遇到问题 - 多个表上的 CASCADE ON DELETE 规则.显然,CASCADE ON DELETE"规则的执行顺序取决于它们的定义顺序.
I have a problem with MySQL - CASCADE ON DELETE rules on multiple tables. Obviously the execution order of "CASCADE ON DELETE"-rules depends on their order of definition.
但是这个执行顺序是明确定义的还是取决于 MySQL 版本?
这是我的三个表A"、B"、C"的简单示例
Here is my simple example for three tables "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"中唯一的条目:
...and try to delete the only entry in table "A":
delete from A where id=1;
...我收到以下错误消息:
...I get the following error message:
Cannot delete or update a parent row: a foreign key constraint fails (`C`, CONSTRAINT `C2` FOREIGN KEY (`b_id`) REFERENCES `B` (`id`))
但是如果我将外键约束的定义更改为:
But if I change the definition of the foreign key constraints to:
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;
...一切都很好...
...everything is fine...
感谢您的帮助
推荐答案
你的ALTER TABLE table ADD CONSTRAINT foreign_key FOREIGN KEY
是一样的,所以它们不是错误的原因.
Your ALTER TABLE table ADD CONSTRAINT foreign_key FOREIGN KEY
are the same, so they cannot be the reason of the error.
(...文本被删除)
无法解释这种奇怪的行为.可以建议一个解决方法:
Cannot explain that strange behavior. Can suggest a workaround:
SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM A WHERE ID=1;
SET FOREIGN_KEY_CHECKS = 1;
这篇关于MySQL - 删除级联上的外键 - 是否有定义的执行顺序?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!