Foreign key constraint may cause cycles or multiple cascade paths?

Question

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.

Microsoft themselves advises the use of triggers instead of FK constraints.

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.

Master { ID, masterfields }

Detail1 { ID, MasterID, detail1fields }

Detail2 { ID, MasterID, detail2fields }

SomeOtherTable {ID, Detail1ID, Detail2ID, someothertablefields }

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:

Master { ID, masterfields }

DetailMain { ID, MasterID }

Detail1 { DetailMainID, detail1fields }

Detail2 { DetailMainID, detail2fields }

SomeOtherTable {ID, DetailMainID, someothertablefields }

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).

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.

There is an article available in which explains how to perform multiple deletion paths using triggers. Maybe this is useful for complex scenarios.

http://www.mssqltips.com/sqlservertip/2733/solving-the-sql-server-multiple-cascade-path-issue-with-a-trigger/

By the sounds of it you have an OnDelete/OnUpdate action on one of your existing Foreign Keys, that will modify your codes table.

So by creating this Foreign Key, you'd be creating a cyclic problem,

E.g. Updating Employees, causes Codes to changed by an On Update Action, causes Employees to be changed by an On Update Action... etc...

If you post your Table Definitions for both tables, & your Foreign Key/constraint definitions we should be able to tell you where the problem is...

This is because Emplyee might have Collection of other entity say Qualifications and Qualification might have some other collection Universities e.g.

public class Employee{
public virtual ICollection<Qualification> Qualifications {get;set;}

}

public class Qualification{

public Employee Employee {get;set;}

public virtual ICollection<University> Universities {get;set;}

}

public class University{

public Qualification Qualification {get;set;}

}

On DataContext it could be like below

protected override void OnModelCreating(DbModelBuilder modelBuilder){

modelBuilder.Entity<Qualification>().HasRequired(x=> x.Employee).WithMany(e => e.Qualifications);
modelBuilder.Entity<University>.HasRequired(x => x.Qualification).WithMany(e => e.Universities);

}

in this case there is chain from Employee to Qualification and From Qualification to Universities. So it was throwing same exception to me.

It worked for me when I changed

    modelBuilder.Entity<Qualification>().**HasRequired**(x=> x.Employee).WithMany(e => e.Qualifications); 

To

    modelBuilder.Entity<Qualification>().**HasOptional**(x=> x.Employee).WithMany(e => e.Qualifications);