MySQL: ON UPDATE CASCADE for a simple table "id|parent|text", not possible?
I have a following table:
CREATE TABLE IF NOT EXISTS `Tree` (
`id` int开发者_Go百科(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
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):
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.
精彩评论