Foreign key constraint may cause cycles or multiple cascade paths

Question

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   Any ideas how I can do this

User · Answer

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

User · Answer

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

User · Answer

Trigger is solution for this problem   IF OBJECT ID  dbo fktest2    U   IS NOT NULL     drop table fktest2 IF OBJECT ID  dbo fktest1    U   IS NOT NULL     drop table fktest1 IF EXISTS  SELECT name FROM sysobjects WHERE name    fkTest1Trigger  AND type    TR       DROP TRIGGER dbo fkTest1Trigger go create table fktest1  id int primary key  anQId int identity  go       create table fktest2  id1 int  id2 int  anQId int identity          FOREIGN KEY  id1  REFERENCES fktest1  id              ON DELETE CASCADE             ON UPDATE CASCADE                FOREIGN KEY  id2  REFERENCES fktest1  id  this causes compile error so we have to use triggers             ON DELETE CASCADE             ON UPDATE CASCADE                  go  CREATE TRIGGER fkTest1Trigger ON fkTest1 AFTER INSERT  UPDATE  DELETE AS     if   ROWCOUNT   0         return     set nocount on      -- This code is replacement for foreign key cascade  auto update of field in destination table when its referenced primary key in source table changes      -- Compiler complains only when you use multiple cascased  It throws this compile error      -- Rrigger Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths  Specify ON DELETE NO ACTION or ON UPDATE NO ACTION       -- or modify other FOREIGN KEY constraints      IF   UPDATE  id  and exists select 1 from fktest1 A join deleted B on B anqid   A anqid where B id  lt  gt  A id        begin                update fktest2 set id2   i id             from deleted d             join fktest2 on d id   fktest2 id2             join inserted i on i anqid   d anqid             end              if exists  select 1 from deleted                 DELETE one FROM fktest2 one LEFT JOIN fktest1 two ON two id   one id2 where two id is null -- drop all from dest table which are not in source table GO  insert into fktest1  id  values  1  insert into fktest1  id  values  2  insert into fktest1  id  values  3   insert into fktest2  id1  id2  values  1 1  insert into fktest2  id1  id2  values  2 2  insert into fktest2  id1  id2  values  1 3   select   from fktest1 select   from fktest2  update fktest1 set id 11 where id 1 update fktest1 set id 22 where id 2 update fktest1 set id 33 where id 3 delete from fktest1 where id  gt  22  select   from fktest1 select   from fktest2

User · Answer

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   amp  your Foreign Key constraint definitions we should be able to tell you where the problem is

User · Answer

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

User · Answer

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

User · Answer

My solution to this problem encountered using ASP NET Core 2 0 and EF Core 2 0 was to perform the following in order    Run update-database command in Package Management Console  PMC  to create the database  this results in the  Introducing FOREIGN KEY constraint     may cause cycles or multiple cascade paths   error  Run script-migration -Idempotent command in PMC to create a script that can be run regardless of the existing tables constraints Take the resulting script and find ON DELETE CASCADE and replace with ON DELETE NO ACTION Execute the modified SQL against the database   Now  your migrations should be up-to-date and the cascading deletes should not occur   Too bad I was not able to find any way to do this in Entity Framework Core 2 0   Good luck

User · Answer

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 lt Qualification gt  Qualifications  get set        public class Qualification   public Employee Employee  get set    public virtual ICollection lt University gt  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 lt Qualification gt    HasRequired x  gt  x Employee  WithMany e   gt  e Qualifications   modelBuilder Entity lt University gt  HasRequired x   gt  x Qualification  WithMany e   gt  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 lt Qualification gt      HasRequired   x  gt  x Employee  WithMany e   gt  e Qualifications      To       modelBuilder Entity lt Qualification gt      HasOptional   x  gt  x Employee  WithMany e   gt  e Qualifications

User · Answer

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   protected override void OnModelCreating  DbModelBuilder modelBuilder         modelBuilder Entity lt TableName gt    HasMany i   gt  i Member  WithRequired   WillCascadeOnDelete false       Or Turn off this feature completely   modelBuilder Conventions Remove lt OneToManyCascadeDeleteConvention gt

[sql] Foreign key constraint may cause cycles or multiple cascade paths?

Examples related to sql

Examples related to sql-server

Examples related to constraints