mysql - DELETE CASCADE 无法自引用深度超过 15 级的 MySQL 表

Question

我有一个带有自引用外键的 MySQL 表。 ON DELETE CASCADE 在其中工作得非常好，但我注意到一个奇怪的行为，它对父实体最多只能工作 14 个级别。一旦我添加了一个第 15 级的 child 并尝试删除父级，它就会开始抛出错误

"Cannot delete or update a parent row: a foreign key constraint fails"

这是层次结构的图像。

尝试删除 Parent 会抛出错误。

• 删除 Child15 时，可以删除 Parent
• 在不删除 Child15 的情况下，如果我尝试删除 Child1，则会成功删除它。

以下示例(也可作为 fiddle 获得)在删除具有 15 个后代的行时重现错误:

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `parent` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_table1_1` (`parent`),
  CONSTRAINT `FK_table1_1` FOREIGN KEY (`parent`) REFERENCES `table1` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


INSERT INTO table1 (id, name, parent)
  VALUES
(1, "Parent", null),
(2, "Child 1", 1),
(3, "Child 2", 2),
(4, "Child 3", 3),
(5, "Child 4", 4),
(6, "Child 5", 5),
(7, "Child 6", 6),
(8, "Child 7", 7),
(9, "Child 8", 8),
(10, "Child 9", 9),
(11, "Child 10", 10),
(12, "Child 11", 11),
(13, "Child 12", 12),
(14, "Child 13", 13),
(15, "Child 14", 14),
(16, "Child 15", 15)
;

-- generates the error
DELETE FROM table1 WHERE id=1;

如果删除了包含 14 个后代的行，则没有错误:

DELETE FROM table1 WHERE id=2;

我知道删除它的可能解决方法

• 设置 FOREIGN_KEY_CHECKS=0
• 删除时自下而上

但我想知道这是 MySQL 对 ON CASCADE DELETE 的一些已知限制吗？

我正在使用 MySQL 服务器版本 5.6

Answer 1

这是记录在案的行为:

If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the sametable it has previously updated during the cascade, it acts likeRESTRICT. This means that you cannot use self-referential ON UPDATECASCADE or ON UPDATE SET NULL operations. This is to prevent infiniteloops resulting from cascaded updates. A self-referential ON DELETESET NULL, on the other hand, is possible, as is a self-referential ONDELETE CASCADE. Cascading operations may not be nested more than 15levels deep.

来源:InnoDB and FOREIGN KEY Constraints, Referential Actions