MySQL:对简单表 "id|parent|text"进行更新级联，不可能吗？

Question

我有下表:

CREATE TABLE IF NOT EXISTS `Tree` (
  `id` int(10) NOT NULL,
  `parent` int(10) DEFAULT NULL,
  `text` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Tree` (`id`, `parent`, `text`) VALUES
(1, 1, '1'),
(2, 1, '1.1'),
(3, 1, '1.2'),
(4, 1, '1.3');

ALTER TABLE `Tree` ADD CONSTRAINT `tree_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `tree` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

执行完上述所有语句后，观察到以下语句的问题:

UPDATE  `Tree` SET  `id` =  '10' WHERE  `Tree`.`id` = 1

由于 ON UPDATE CASCADE 约束，预计更改主 ID 会导致所有链接的 parent 记录自动更新。这些级联的参照完整性约束不就是全部吗？

Answer 1

The docs说以下(强调我的):

Deviation from SQL standards: If ON UPDATE CASCADE or ON UPDATE SET NULL recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE or ON UPDATE SET NULL operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential ON DELETE SET NULL, on the other hand, is possible, as is a self-referential ON DELETE CASCADE. Cascading operations may not be nested more than 15 levels deep.