Ytukyg

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

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 <> 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 > 22



select * from fktest1

select * from fktest2

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<TableName>().HasMany(i => i.Member).WithRequired().WillCascadeOnDelete(false);

}

Or Turn off this feature completely:

modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();

My solution to this problem encountered using ASP.NET Core 2.0 and EF Core 2.0 was to perform the following in order:

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




2. Run script-migration -Idempotent command in PMC to create a script that can be run regardless of the existing tables/constraints




3. Take the resulting script and find ON DELETE CASCADE and replace with ON DELETE NO ACTION




4. 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!