0

I have four entities A, B, C, and D.

A has many Bs and Cs.

B has many Ds.

And there is a many-to-many relationship between C and D.

This many-to-many relationship is in a join table exposed to Breeze as an entity CD.

I want to establish a cascading delete so that deleting the first entity in any of the following rows yields that cascading delete.

A -> B -> D -> CD

B -> D -> CD

D -> CD

A -> C -> CD

C -> CD

All the other foreign keys have a cascading delete except for A -> C. Every time I attempt to set this as a cascading delete SQL Server 2012 gives me the following error:

Unable to create relationship 'FK_dbo.C_dbo.A_AId'. Introducing FOREIGN KEY constraint 'FK_dbo.C_dbo.A_AId' on table 'C' 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. See previous errors.

How can I resolve this error and get close to what I'm looking for?

user1569339
  • 683
  • 8
  • 20
  • possible duplicate of [Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?](http://stackoverflow.com/questions/17127351/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) – Colin Nov 01 '14 at 08:27

1 Answers1

0

As there multiple cascade paths, SQL server is complaining about ON DELETE CASCADE OPTION

one path C->CD and another C->A->B->D->CD

you can just define foreign key with out ON DELETE CASCADE option

you can create INSETAD OF DELETE trigger on the table C and delete the corresponding row in table A

radar
  • 13,270
  • 2
  • 25
  • 33
  • Perhaps my use of arrows was not correct. CD holds a foreign key to both C and D, not the other way around. How would I deal with that then? Create a BEFORE DELETE trigger on A that deletes the row in C? – user1569339 Nov 03 '14 at 17:59
  • Oops, I meant INSTEAD OF DELETE in my comment above. Also this still won't work for me because A is itself has a FK with a cascade delete. Looks like I'll have to push the delete logic to the application. – user1569339 Nov 03 '14 at 18:53
  • @user1569339, yes you are right, have a stored procedure which does this deletion and call it from your application layer. – radar Nov 03 '14 at 19:21
  • Or perhaps I could have the records in C orphaned and delete them subsequently with an AFTER DELETE trigger. – user1569339 Nov 03 '14 at 19:41