MySQL:在简单表"id | parent | text"的ON UPDATE CASCADE上，不可能吗? [英] MySQL: ON UPDATE CASCADE for a simple table "id|parent|text", not possible?

问题描述

我有一张下表:

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;

执行完所有上述语句后，发现以下问题:

After having executed all of the above statements a problem observed for the following one:

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

尽管由于ON UPDATE CASCADE约束，预期更改主ID将导致所有链接的parent记录自动更新.这些级联的参照完整性约束不是全部吗?

While it is expected that changing the main id would cause all linked parent records to update automatically due to ON UPDATE CASCADE constraint. Aren't these cascading referential integrity constraints are all about?

推荐答案

文档说以下内容(强调我的意思):

The docs say the following (emphasis mine):

与SQL标准的差异:如果为ON UPDATE CASCADE或ON UPDATE SET NULL 递归更新同一张表 之前在 级联，它的行为类似于RESTRICT. 此 表示您不能使用 自引用ON UPDATE CASCADE或 ON UPDATE SET NULL操作.这是 防止产生无限循环 从级联更新.一种 自引用的ON DELETE SET NULL， 另一方面，是可能的 自我引用的DELETE CASCADE. 级联操作可能不嵌套 超过15个层次.

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.

这篇关于MySQL:在简单表"id | parent | text"的ON UPDATE CASCADE上，不可能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！