24

I have a problem where i need a cascade on multiple foreign keys pointing to the same table..

[Insights]
| ID | Title        |
| 1  | Monty Python |
| 2  | Spamalot     | 

[BroaderInsights_Insights]
| broaderinsight_id | insight_id |
| 1                 | 2          |

Basically when either record one or two in the insights table is deleted i need the relationship to also be deleted..

I've tried this:

 CREATE TABLE broader_insights_insights(id INT NOT NULL IDENTITY(1,1),
   broader_insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   insight_id INT NOT NULL REFERENCES insights(id) ON DELETE CASCADE,
   PRIMARY KEY(id))
Go

This results in the warning that the cascade "may cause cycles or multiple cascade path"

So ive tried adding a cascade to just the insight_id and this results in:

The DELETE statement conflicted with the REFERENCE constraint

Any ideas?

Thanks

Daniel

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
Daniel Upton
  • 5,561
  • 8
  • 41
  • 64
  • 1
    Such a cascading delete is rarely a good idea. Think of what would happen if you also have "Life of Brian" in there. Delete LoB, and the delete cascades to kill off Monty Python, which cascades to delete Spamalot. Since it's a circular relationship, deleting any member of the circle deletes the entire circle. – Marc B Feb 16 '11 at 15:10
  • 1
    Am i misunderstanding cascades? i thought the concept was when the referenced record was deleted the relationship is also deleted? how would you suggest achieving this? – Daniel Upton Feb 16 '11 at 15:17
  • You need to do the deletes in one transaction (easiest i na stored proc) rather than through cascade deletes. – HLGEM Feb 16 '11 at 15:19
  • 3
    @Marc B - Cascades only work in one direction. Deleting LoB would delete any reference from this linking table to LoB, but they wouldn't affect any other rows in Insights. – Damien_The_Unbeliever Feb 16 '11 at 15:21
  • 7
    The need for multiple cascade paths is ubiquitous and perfectly legitimate. Practically any time you have a many-to-many relationship you need it. Say you have Parts, Vendors, and PartVendors. If you delete a Part, you want to delete any rows in PartVendors for that part. If you delete a Vendor, you want to delete any rows in PartVendors for that Vendor. But SQL Server doesn't support this. It's supper annoying, no other serious DB engine has this problem, people complained about it in 2005, Microsfot agreed that it was a "desirable feature" in 2008, but still here in 2014 they don't have it. – Shavais Aug 05 '14 at 21:28
  • The very last thing I want to do is go adding a custom trigger to half of all the tables in all my databases that need to participate in many-to-many relationships. – Shavais Aug 05 '14 at 21:31

1 Answers1

32

You'll have to implement this as an INSTEAD OF delete trigger on insights, to get it to work. Something like:

create trigger T_Insights_D
on Insights
instead of delete
as
    set nocount on
    delete from broader_insights_insights
    where insight_id in (select ID from deleted) or
    broader_insight_id in (select ID from deleted)

    delete from Insights where ID in (select ID from deleted)

Frequently with cascading deletes and lots of foreign keys, you need to spend time to work out a "cascade" order so that the delete that occurs at the top of a "tree" is successfully cascaded to referencing tables. But that isn't possible in this case.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448