Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 821
Vilx- Avatar asked Oct 28 '09 14:10

Vilx-


People also ask

What is Cascade option in foreign key?

What is a foreign key with Cascade DELETE in SQL Server? A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.

What does cascade constraints mean in SQL?

Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted.

How do I add a cascade to a foreign key?

If you want to add an on delete cascade to an existing foreign key constraint, you are going to need two statements. The first statement will drop the constraint and the second statement will recreate it with the addition of the on delete clause.

What is difference between restrict and Cascade?

The CASCADE option directs the DBMS Server to revoke the specified privileges plus all privileges and objects that depend on the privileges being revoked. The RESTRICT option directs the DBMS Server not to revoke the specified privilege if there are any dependent privileges or objects.


Video Answer


1 Answers

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.

like image 67
gbn Avatar answered Oct 24 '22 00:10

gbn