3

Here's my database for a notification system (it has been stripped down for simplicity). When the a User is deleted I want the related Notification and UserEmail to be deleted but I get the constraint may cause cycles or multiple cascade paths error when I try to set the relationship to ON DELETE Cascade.

Unable to create relationship 'FK_Notification_TargetUser'.  
Introducing FOREIGN KEY constraint 'FK_Notification_TargetUser' on table 'Notification' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

database diagram

After doing some research my possible solutions are:

Any recommendations for what I should do?

Additional Resources:

Community
  • 1
  • 1
user3811205
  • 195
  • 2
  • 13
  • 1
    Delete rows manually from dedicated stored procedure or application, what is the problem? Is cascading an universal panacea? – Giorgi Nakeuri Apr 09 '15 at 19:13
  • don't you want to cascade from user to notifications? this does not make sense to me, why should deleting a notification cause a user to be deleted. – Hogan Apr 09 '15 at 19:21
  • @Hogan : Yes that's what I meant. Sorry that it wasn't clear. – user3811205 Apr 09 '15 at 19:43
  • you don't have to be clear to me... just to sql-server. – Hogan Apr 09 '15 at 19:48
  • 1
    I'd go with the trigger. ...at least out of the two choices you're considering. I'd actually go with a soft-delete model if it were solely up to me. – Tab Alleman Apr 09 '15 at 19:49
  • Just for the record I think soft-delete model is better. – Hogan Apr 09 '15 at 21:31
  • @TabAlleman Thanks for the recommendation. I guess another bonus of using that approach is that I would get a history of notifications. – user3811205 Apr 10 '15 at 00:09

0 Answers0