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 ---> account.category_id account.id ---> credential.account_id category.id ---> field.category_id field.id ---> 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?