1

Note: this question is more about database design and SQL Server than specific ORMDB like EF Core.

I have a database schema that looks like this:

enter image description here

Using EF Core Migration, all the SQL statements can be run until the very last constraint (FK_BookReleases_Nicknames_NicknameId). The previous FK FK_BookReleases_Books_BookId could be added.

The error I receive is (as many other articles on SO):

Introducing FOREIGN KEY constraint 'FK_BookReleases_Nicknames_NicknameId' on table 'BookReleases' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

My question is, where is the flaw on that design? I cannot understand under any logic such situation happens. And what is the proper fix? I see many suggested changing ON DELETE action but not specific on which FK and what other action I should use.

Worse, even already read this article I still don't understand how it could be a problem, and how would his proposed solution fits in the above schema.

Luke Vo
  • 17,859
  • 21
  • 105
  • 181
  • See the answer here: https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths/3548225#3548225 There is really nothing wrong with the design, it's an implementation restriction. – George Barwood Apr 12 '20 at 21:41
  • @GeorgeBarwood Sorry maybe night shift clouds my mind. I actually quoted that answer in my question, the problem is, I cannot understand how his workaround works. i.e on the above case, how should my tables look like? Or what FK Action should I change? – Luke Vo Apr 12 '20 at 22:01
  • I think I will set No Action on Author-Nickname FK relationship, assuming that Nickname will never be deleted, or that if Author is ever be deleted, all nicknames must be deleted first. – Luke Vo Apr 12 '20 at 22:04
  • Please clarify via edits, not comments. Put what is needed to ask your question directly in it. If you are trying to ask about uderstanding that presentation then make that clear & quote what's needed to explain about how you are stuck. Or comment on that post for clarification. Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please for code questions give a [mre]. – philipxy Apr 12 '20 at 22:15
  • "may cause cycles or multiple cascade paths" Read the manual--multiple paths are not allowed. Find out what it means by "path". Follow the cascade paths from Author--there are two to AuthorRelease. How are you stuck in this process? Or if you are stuck following the solution per your comment then your question is not actually what your post says it is so edit your post to ask the 1 question you want to ask. – philipxy Apr 12 '20 at 22:20

1 Answers1

1

The issue is due to multiple cascading paths from Author (Grand Parent) to BookRelease (Grand Child). There are two cascading paths:

  1. Author -> Book -> BookRelease
  2. Author -> Nickname -> BookRelease

This is discussed in detail in the post in MSSQLTips

Multiple Cascading Paths

So, the way to handle this is:

  1. Disable the ON DELETE CASCAE and choose NOACTION as the foreign key creation.

  2. Create INSTEAD OF DELETE TRIGGERS in Author(GrandParent), Book(Child1), Nickname(Child2) tables to handle the deletion of parent keys in the child tables.

  • Grand Parent deletion : Delete in GrandChild, followed by Child1, followed by Child2,
  • Child1 deletion: Delete in GrandChild, followed by Child1
  • Child2 deletion: Delete in GrandChild, followed by Child2
Venkataraman R
  • 12,181
  • 2
  • 31
  • 58