Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

enter image description here

Now, I'm attempting to set CASCADE delete rules like so: ---> account.category_id ---> credential.account_id ---> field.category_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?

share|improve this question
Why are you using CASCADE? Use a trigger. – Aaron Bertrand Mar 27 '14 at 19:24
I'm using SQL Server Compact Edition which doesn't support triggers. – SeeSharpCode Mar 27 '14 at 19:48
Sorry, missed the tag. shrug Does compact edition support delete statements you write yourself, in the right order, rather than relying on cascade? – Aaron Bertrand Mar 27 '14 at 19:54
Yes, it does. I was hoping to avoid that route but it looks like it might be the only solution (also making it the best solution =D). – SeeSharpCode Mar 27 '14 at 19:56
By the way, that is not (and should not be) called "Cyclical Reference", there is no cycle. It's "Multiple Cascade Paths". – ypercubeᵀᴹ Mar 27 '14 at 21:54
up vote 3 down vote accepted

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
share|improve this answer
That sounds fair to me. I'm too far in to switch platforms, but manually writing the DELETE statements seems like a viable option to me. Thanks. – SeeSharpCode Mar 27 '14 at 20:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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