MS SQL “ON DELETE CASCADE” multiple foreign keys pointing to the same table?

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.

Leave a Comment