Take the 2-minute tour ×
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.

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:

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?

share|improve this question
    
Why are you using CASCADE? Use a trigger. –  Aaron Bertrand Mar 27 at 19:24
    
I'm using SQL Server Compact Edition which doesn't support triggers. –  Breems Mar 27 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 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). –  Breems Mar 27 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 at 21:54

1 Answer 1

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. –  Breems Mar 27 at 20:16

Your Answer

 
discard

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.