Resolving Cyclical Reference in Referential Relationships I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an account/password manager.

Here are my relationships:



Now, I'm attempting to set CASCADE delete rules like so:

category.id ---&gt; account.category_id
account.id ---&gt; credential.account_id
category.id ---&gt; field.category_id
field.id ---&gt; credential.field_id


After adding the first 3 relationships, I get an error while adding the 4th (field ---> credential):

The referential relationship will result in a cyclical reference that is not allowed.

I'm struggling to determine why this is happening and how to fix it. Any ideas?
Since you are using SQL Server Compact Edition, which doesn't support triggers that could solve this issue, my recommendation is either:


write your own manual DELETE statements in the proper order, rather than use CASCADE
change the design
use Express / LocalDB instead of Compact Edition, which is extremely limited and has been deprecated