是否可以在自引用表中对分层数据使用约束？
假设您有下表，旨在表示分层数据：
+--------+-------------+
| Field | Type |
+--------+-------------+
| id | int(10) |
| parent | int(10) |
| name | varchar(45) |
+--------+-------------+
该表是自引用的，因为 parent_id 引用 id。
因此，您可能有以下数据：
+----+--------+---------------+
| id | parent | name |
+----+--------+---------------+
| 1 | 0 | fruit |
| 2 | 0 | vegetable |
| 3 | 1 | apple |
| 4 | 1 | orange |
| 5 | 3 | red delicious |
| 6 | 3 | granny smith |
| 7 | 3 | gala |
+----+--------+---------------+
使用 MySQL，我尝试对数据施加（自引用）外键约束，以在更新时级联并防止删除记录（如果有任何“”）孩子们。”
所以我使用了以下内容：
CREATE TABLE `test`.`fruit` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent` INT(10) UNSIGNED,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_parent`
FOREIGN KEY (`parent`)
REFERENCES `fruit` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
ENGINE = InnoDB;
据我了解，这应该符合我的要求。 （并且 parent 必须默认为 null 才能允许插入，对吗？）
问题是，如果我更改记录的 id，它不会级联：
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fruit`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE)
我缺少什么？
如果我的术语搞砸了，请随时纠正我......我对约束很陌生。
Suppose you have the following table, intended to represent hierarchical data:
+--------+-------------+
| Field | Type |
+--------+-------------+
| id | int(10) |
| parent | int(10) |
| name | varchar(45) |
+--------+-------------+
The table is self-referential in that the parent_id refers to id.
So you might have the following data:
+----+--------+---------------+
| id | parent | name |
+----+--------+---------------+
| 1 | 0 | fruit |
| 2 | 0 | vegetable |
| 3 | 1 | apple |
| 4 | 1 | orange |
| 5 | 3 | red delicious |
| 6 | 3 | granny smith |
| 7 | 3 | gala |
+----+--------+---------------+
Using MySQL, I am trying to impose a (self-referential) foreign key constraint upon the data to cascade on update and prevent deletion of a record if it has any "children."
So I used the following:
CREATE TABLE `test`.`fruit` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`parent` INT(10) UNSIGNED,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_parent`
FOREIGN KEY (`parent`)
REFERENCES `fruit` (`id`)
ON UPDATE CASCADE
ON DELETE RESTRICT
)
ENGINE = InnoDB;
From what I understand, this should fit my requirements. (And parent must default to null to allow insertions, correct?)
The problem is, if I change the id of a record, it will not cascade:
Cannot delete or update a parent row: a foreign key constraint fails (`test`.`fruit`, CONSTRAINT `fk_parent` FOREIGN KEY (`parent`) REFERENCES `fruit` (`id`) ON UPDATE CASCADE)
What am I missing?
Feel free to correct me if my terminology is screwed up... I'm new to constraints.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
偏离 SQL 标准：如果 ON UPDATE CASCADE 或 ON UPDATE SET NULL 递归更新之前在级联期间更新过的同一个表，则其作用类似于 RESTRICT。这意味着您不能使用自引用 ON UPDATE CASCADE 或 ON UPDATE SET NULL 操作。这是为了防止级联更新导致无限循环。另一方面，自引用 ON DELETE SET NULL 是可能的，自引用 ON 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.