SQL Server "Delete on table may cause cycles or multiple cascade paths"

Question

We have ASP.NET MVC2 project with EntityFramework mappings.

How to delete row from Structure table with cascade deleting rows from Customer table (Customer table has "Zero or One - Many" association with itself and cascade deleteing of children Customers)?

Answer 1

Cascading Deletes is something that I always stay away from. It is a cool concept to learn about, but I have not yet seen a serious implementation of Cascading Deletes.

Having read my rant, read on..

When you execute your delete statement, SQL Server first identifies all the rows that have to be deleted. It builds a structure of all tables that have to be included, and all the rows that will be deleted.

Any given table cannot appear in this list more than once, and there must be a single path to a child tree from a parent tree.

I think that you are encountering this issue because you have a self join on the Customer table.

You are going to have to delete the child rows by hand, and then delete the parent.

FWIW, I would not drop the foreign key relationship and try to set up triggers (like the KB article below suggests).

http://support.microsoft.com/kb/321843