8

You can see that many questions have being asked about multiple cascade paths. For example:

https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths

https://stackoverflow.com/questions/6065501/multiple-cascade-delete-path-in-many-many-relationship-ef-4-1

https://stackoverflow.com/questions/27613117/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths-s

However, from what I see and understand, it's totally OK that you want to delete a child record on many and not just one condition of the deletion of the related master records.

While in a question it's said that SQL Server tries to be safe by preventing this from happening, I really don't get it that what might go wrong if we have multiple cascade paths, and what problems it prevents to make it safe?

I hope someone can explain that to me, in plain and simple words, preferably using examples of what might go wrong in case of multiple cascade paths.

5

This question "what can go wrong" can only be answered by the developers at Microsoft or Sybase. When there are multiple cascade paths to the same record, it's possible that the code might attempt to delete it multiple times. If it isn't constructed to deal with the possibility that the record to be deleted has already been deleted, it might throw an error. Apparently this problem actually exists (or is thought to exist!), so instead of fixing it, the implementation avoids it by preventing the definition of duplicate cascade paths. It's clearly a short-cut, because other DBMS have no problem with this situation.

  • 2
    you'd think that after 20 years of SQL Server, they'd get around to fixing this problem... – Jeremy Holovacs Oct 3 '18 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.