4

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.

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".

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.

enter image description here

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • You can use a CTE in a trigger to determine all of the related rows and delete them together. – HABO Nov 11 '13 at 16:19
  • 3
    The error message says "*may* cause cycles...", which means that technically there *is* such a possibility. A DISPLAY_TAB row could be both a child and a parent in DISPLAY_TAB_GROUPING, and deleting it would indeed cause multiple cascade paths if both ON CASCADE DELETE settings were in effect. – Andriy M Nov 11 '13 at 16:41
  • @AndriyM thanks for clarification, not I see. Logically a child cannot be own parent, but I can see how technically it is possible. – Yuriy Galanter Nov 11 '13 at 17:15
  • I meant it could be one row's parent and another's child. – Andriy M Nov 11 '13 at 17:16
  • @AndriyM in my case it's a one-level relationship, but I see your point. – Yuriy Galanter Nov 11 '13 at 17:17

1 Answers1

4

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

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.

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.

dazedandconfused
  • 3,131
  • 1
  • 18
  • 29