CASCADE Remove in many places for self-promotion

Question

The DISPLAY_TAB table below is a self-promotion 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 would like to establish a CASCADE DELETE relationship between the main table and the relationship table DISPLAY_TAB_GROUPING , so when the parent or child tab is deleted, the relationship is also automatically deleted (only the relationship, not the actual tab record). Therefore, I create a FOREIGN KEY constraint on DISPLAY_TAB_GROUPING for the fields TAB_ID_R_1 and TAB_ID_R_2, referring to TAB_ID in the DISPLAY_TAB table. And it works great, it works great when I add ON DELETE CASCADE for one of the relationships, but when I try to do it for both - it throws an error that this may cause loops or several cascading paths.

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

Answer 1

Read this article , which states, among other things, the following:

This error message was received because in SQL Server the table cannot appear more than once in the list of all cascading referenced actions that are started using the DELETE or UPDATE command. For example, a tree of cascading referenced actions should have only one path to a specific table by cascading referenced actions tree.

To do what you want, the DISPLAY_TAB_GROUPING table should appear twice. I suggest you use a stored procedure that implements your delete code.