多对多自引用表中的 CASCADE 删除 [英] CASCADE Delete in many-to-many self-reference table

问题描述

表 DISPLAY_TAB 下面是一个自引用表，可以包含父标签和子标签.一个父选项卡可以有多个子选项卡，一个子选项卡可以属于多个父选项卡.

Table DISPLAY_TAB below is a self-reference table that can contain both parent and child tabs. A parent tab can have multiple child tabs and a child tab can belong to multiple parents.

我想在主表和关系表 DISPLAY_TAB_GROUPING 之间建立 CASCADE DELETE 关系，所以当父标签或子标签被删除时 - 关系也会自动删除(只是关系，而不是实际标签记录).因此，我在 DISPLAY_TAB_GROUPING 上为字段 TAB_ID_R_1 和 TAB_ID_R_2 表创建 FOREIGN KEY 约束，引用 DISPLAY_TAB 表中的 TAB_ID.它工作正常，当我为其中一个关系添加 ON DELETE CASCADE 时，它工作正常，但是当我对两者都尝试时 - 它会抛出一个错误，即 可能导致循环或多个级联路径".

I'd like to establish a CASCADE DELETE relationship between main table and relationship table DISPLAY_TAB_GROUPING so when either parent or child tab is deleted - relationship is automatically deleted as well (just relationship, not actual tab record). So I am creating a FOREIGN KEY constrain on DISPLAY_TAB_GROUPING for fields TAB_ID_R_1 and TAB_ID_R_2 tables, referencing TAB_ID in DISPLAY_TAB table. And it works fine, it works fine when I add ON DELETE CASCADE for one of the relationship, but when I try it for both - it throws an error that this "May cause cycles or multiple cascade paths".

如何为这两种关系设置 ON CASCADE DELETE?我看不出这会如何导致循环 - 每次删除主表中的记录时，只应删除关系表中的相关记录.

How do I set ON CASCADE DELETE for both relationships? I don't see how this can cause cycles - every time a record in main table is deleted only related record(s) in relationship tables should be deleted.

推荐答案

阅读 this 知识库文章，其中包含以下内容...

Read this KB article, which says the following among other things...

您收到此错误消息是因为在 SQL Server 中，表不能在所有级联引用的列表中出现多次由 DELETE 或 UPDATE 语句启动的操作.例如，级联引用操作的树必须只有级联引用操作上特定表的一条路径树.

You receive this error message because in SQL Server, a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

要执行您想要的操作，DISPLAY_TAB_GROUPING 表必须出现两次.我建议您改用实现删除代码的存储过程.

To do what you want, the DISPLAY_TAB_GROUPING table would have to appear twice. I suggest you use a stored proc that implements your delete code instead.

这篇关于多对多自引用表中的 CASCADE 删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！