What is the problem with foreign key cascade multiple paths and cycles?

sql foreign-keys cascade rdbms-agnostic
Vilx- picture Vilx- · Oct 28, 2009 · Viewed 9.8k times · Source

In SQL Server 2005 I just struck the infamous error message:

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

Now, StackOverflow has several topics about this error message, so I've already got the solution (in my case I'll have to use triggers), but I'm curious as to why there is such a problem at all.

As I understand it, there are basically two scenarios that they want to avoid - a cycle and multiple paths. A cycle would be where two tables have cascading foreign keys to each other. OK, a cycle can span several tables too, but this is the basic case and will be easier to analyze.

Multiple paths would be when TableA has foreign keys to TableB and TableC, and TableB also has a foreign key to TableC. Again - this is the minimum basic case.

I cannot see any problems that would arise when a record would get deleted or updated in any of those tables. Sure, you might need to query the same table multiple times to see which records need updating/deleting, but is that really a problem? Is this a performance issue?

In other SO topics people go as far as to label using cascades as "risky" and state that "resolving cascade paths is a complex problem". Why? Where is the risk? Where is the problem?

Answer

gbn picture gbn · Oct 31, 2009

You have a child table with 2 cascade paths from the same parent: one "delete", one "null".

What takes precedence? What do you expect afterwards? etc

Note: A trigger is code and can add some intelligence or conditions to a cascade.

Related questions

How do the Postgres foreign key 'on update' and 'on delete' options work?

Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them?

Read More
How to CASCADE a delete from a child table to the parent table?

I prepared a fiddle which demonstrates the problem. CREATE TABLE parent ( parent_id integer primary key ); CREATE TABLE child ( child_name TEXT primary key, parent_id integer REFERENCES parent (parent_id) ON DELETE CASCADE ); INSERT INTO parent VALUES (1); INSERT INTO …

Read More
INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

I am getting the following error. Could you please help me? Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table "dbo.Sup_…

Read More