2

Below is the scenario

  • Table1 (Id, Name)

  • Table2 (Id, Name1, Name2, Type)

    • FK1: Name1 references Table1.Name with On delete cascade
    • FK2: Name2 references Table1.Name with On delete cascade

FK2 throws the below error

ERROR:

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

But, in my scenario whether the entity with Name1 or Name2 is deleted the corresponding entry in Table2 should be deleted.

Please help me understand what is the problem this could cause?

Alex
  • 797
  • 10
  • 30
passionatedeveloper
  • 131
  • 1
  • 1
  • 5
  • Is your example correct ? (table names, column names) – Tomasito May 16 '13 at 20:48
  • possible duplicate of [Foreign key constraint may cause cycles or multiple cascade paths?](http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths) – Abe Miessler May 16 '13 at 21:11
  • My example is correct. I have gone through the other questions. I would like to know how I could resolve in my scenario. – passionatedeveloper May 17 '13 at 04:18
  • I recommend NOT doing cascade deletes and instead write a script that handles the deletes for you in a controlled manner. I've seen some really weird things happen with cascade deletes. – DForck42 Oct 27 '16 at 15:31

1 Answers1

3

FK2 wouldn't cause cycles but it would cause multiple cascade paths. A record that is deleted from Table2 would cause the row from Table1 to be deleted twice, once for each foreign key that has deletion marked for cascading. This is disallowed by design. A question like the one you're asking was answered here

"The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default." Retrieved from MSDN Library

Community
  • 1
  • 1
whobetter
  • 65
  • 6
  • This is what I think will happen. Deleting an entry in Table1 would cause the corresponding record in table Table2 to be deleted. How will this cause an issue? – passionatedeveloper May 18 '13 at 07:53
  • The issue would arise when Name1 and Name2 contain the same value. Upon deletion of the name from Table 1, there would be 2 paths down to the same deletion. This is where the "multiple cascade paths" referenced in the error comes in. The 2nd sentence in the snippet above references this, Microsoft won't allow it. – whobetter May 22 '13 at 20:15
  • @passionatedeveloper: If your question has been answered, please mark it as such. – whobetter May 24 '13 at 16:28