sql - CASCADE Delete in many-to-many self-reference table

Question

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.

Answer 1

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.