The problem we are having with cascade deletes is in the case where a table has foreign keys to two different parent tables, which in turn have a common ancestor. Since SQL Server does not allow both FKs to have a cascade, we put a cascade on only one. When we delete the common ancestor the result is inconsistent. Sometimes SQL is able to do it, and sometimes it tries to delete first from the parent without the cascade, and fails with a reference error.

This seems to be unintelligent behavior on the part of SQL Server. Has anyone found a solution for this? Tim's suggestion to use stored procedures is a lot of trouble, but it's the only foolproof solution I've heard.