I have a problem when I try to add constraints to my tables. I get the error:

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

My constraint is between a Code table and an employee table. The Code table contains Id, Name, FriendlyName, Type and a Value. The employee has a number of fields that reference codes, so that there can be a reference for each type of code.

I need for the fields to be set to null if the code that is referenced is deleted.

6 Answers
6

SQL Server does simple counting of cascade paths and, rather than trying to work out whether any cycles actually exist, it assumes the worst and refuses to create the referential actions (CASCADE): you can and should still create the constraints without the referential actions. If you can't alter your design (or doing so would compromise things) then you should consider using triggers as a last resort.

FWIW resolving cascade paths is a complex problem. Other SQL products will simply ignore the problem and allow you to create cycles, in which case it will be a race to see which will overwrite the value last, probably to the ignorance of the designer (e.g. ACE/Jet does this). I understand some SQL products will attempt to resolve simple cases. Fact remains, SQL Server doesn't even try, plays it ultra safe by disallowing more than one path and at least it tells you so.

I came to the same conclution in the end, and ended up solving it using triggers. Nice to know there is this difference between DBMS and that it is in fact a design decision I am stuck on. Thanks a lot :)
–
Christian NielsenMay 12 '09 at 10:37

one thing that i still cannot understand is that, if this "problem" can be solved by using a trigger, then how come that a trigger will not "cause cycles or multiple cascade paths ..." ?
–
armenJul 24 '14 at 10:17

@armen: because your trigger will explicitly supply the logic that the system couldn't implicitly figure out on it's own eg if there are multiple paths for a delete referential action then your trigger code will define which tables are deleted and in which order.
–
onedaywhenAug 5 '14 at 15:22

A typical situation with multiple cascasing paths will be this:
A master table with two details, let's say "Master" and "Detail1" and "Detail2". Both details are cascade delete. So far no problems. But what if both details have a one-to-many-relation with some other table (say "SomeOtherTable"). SomeOtherTable has a Detail1ID-column AND a Detail2ID-column.

In other words: some of the records in SomeOtherTable are linked with Detail1-records and some of the records in SomeOtherTable are linked with Detail2 records. Even if it is guaranteed that SomeOtherTable-records never belong to both Details, it is now impossible to make SomeOhterTable's records cascade delete for both details, because there are multiple cascading paths from Master to SomeOtherTable (one via Detail1 and one via Detail2).
Now you may already have understood this. Here is a possible solution:

All ID fields are key-fields and auto-increment. The crux lies in the DetailMainId fields of the Detail tables. These fields are both key and referential contraint. It is now possible to cascade delete everything by only deleting master-records. The downside is that for each detail1-record AND for each detail2 record, there must also be a DetailMain-record (which is actually created first to get the correct and unique id).

Your comment helped me a lot to understand the problem that I am facing. Thank you! I would prefer turning off the cascade delete for one of the path, then handle delete of other records some other ways(stored procedures; triggers; by code etc). But I keep your solution(grouping in one path) in mind for possible different applications of same problem...
–
freewillFeb 14 '14 at 21:39

One up for use of the word crux (and also for explaining)
–
jtsanDec 11 '14 at 4:35

I would point out that (functionally) there's a BIG difference between cycles and/or multiple paths in the SCHEMA and the DATA. While cycles and perhaps multipaths in the DATA could certainly complicated processing and cause performance problems (cost of "properly" handling), the cost of these characteristics in the schema should be close to zero.

Since most apparent cycles in RDBs occur in hierarchical structures (org chart, part, subpart, etc.) it is unfortunate that SQL Server assumes the worst; i.e., schema cycle == data cycle. In fact, if you're using RI constraints you can't actually build a cycle in the data!

I suspect the multipath problem is similar; i.e., multiple paths in the schema don't necessarily imply multiple paths in the data, but I have less experience with the multipath problem.

Of course if SQL Server did allow cycles it'd still be subject to a depth of 32, but that's probably adequate for most cases. (Too bad that's not a database setting however!)

"Instead of Delete" triggers don't work either. The second time a table is visited, the trigger is ignored. So, if you really want to simulate a cascade you'll have to use stored procedures in the presence of cycles. The Instead-of-Delete-Trigger would work for multipath cases however.

Celko suggests a "better" way to represent hierarchies that doesn't introduce cycles, but there are tradeoffs.

They are fairly long, so I dont think I can post them here, but I would much appreciate your help - dont know if there is some way i can send them to you? Ill try and describe it: The only constraints that exist are from 3 tables that all have fields that reference codes by a simple INT Id key. The problem seems to be that Employee has several fields that reference the code table and that i want them all to cascade to SET NULL. All I need is that when codes are deleted, the references to them should be set to null everywhere.
–
Christian NielsenMay 12 '09 at 8:13

post them anyway... I don't think anyone here will mind, and the code window will format them properly in a scrolling block :)
–
Eoin CampbellMay 12 '09 at 8:30

This is an error of type database trigger policies. A trigger is code and can add some intelligences or conditions to a Cascade relation like Cascade Deletion. You may need to specialize the related tables options around this like Turning off CascadeOnDelete: