Foreign Key Constraint May Cause Cycles or Multiple Cascade Paths

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

Because Stage is required, all one-to-many relationships where Stage is involved will have cascading delete enabled by default. It means, if you delete a Stage entity

• the delete will cascade directly to Side
• the delete will cascade directly to Card and because Card and Side have a required one-to-many relationship with cascading delete enabled by default again it will then cascade from Card to Side

So, you have two cascading delete paths from Stage to Side - which causes the exception.

You must either make the Stage optional in at least one of the entities (i.e. remove the [Required] attribute from the Stage properties) or disable cascading delete with Fluent API (not possible with data annotations):

modelBuilder.Entity<Card>()
    .HasRequired(c => c.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Side>()
    .HasRequired(s => s.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);

FK constraints may cause cycles or multiple cascade paths

The relational diagram clearly shows the multiple cascade path from ProductType to PropertyOptionForProduct:

(1) ProductType -> Product -> PropertyOptionForProduct

(2) ProductType -> ProductProperty -> ProductPropertyOption -> PropertyOptionForProduct

The only solution is to break the cascade path by turning off the cascade delete for at least one of the relationships and then handle the principal entity deletion manually.

Probably the easiest is to break some of the root paths, for instance ProductType -> ProductProperty:

modelBuilder.Entity<ProductType>()
    .HasMany(e => e.Properties)
    .WithOne(e => e.ProductType)
    .OnDelete(DeleteBehavior.Restrict);

Then when you need to delete a ProductType, instead of the "normal":

db.Remove(db.Set<ProductType>().Single(e => e.Id == id));
db.SaveChanges();

you have to first delete the related Properties:

var productType = db.Set<ProductType>().Include(e => e.Properties).Single(e => e.Id == id);
db.RemoveRange(productType.Properties);
db.Remove(productType);
db.SaveChanges();

... may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints

For your current model design, it will create migration below:

            migrationBuilder.AddForeignKey(
            name: "FK_UserRoleRelationship_UserRole_ChildUserRoleId",
            table: "UserRoleRelationship",
            column: "ChildUserRoleId",
            principalTable: "UserRole",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

        migrationBuilder.AddForeignKey(
            name: "FK_UserRoleRelationship_UserRole_ParentUserRoleId",
            table: "UserRoleRelationship",
            column: "ParentUserRoleId",
            principalTable: "UserRole",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

FK_UserRoleRelationship_UserRole_ChildUserRoleId and FK_UserRoleRelationship_UserRole_ParentUserRoleId both will delete the records in UserRole when deleting UserRoleRelationship which will cause multiple cascade delete.

For a workaround, try to make int as int? like below:

        public int? ParentUserRoleId { get; set; }

Which will create

migrationBuilder.AddForeignKey(
                name: "FK_UserRoleRelationship_UserRole_ParentUserRoleId",
                table: "UserRoleRelationship",
                column: "ParentUserRoleId",
                principalTable: "UserRole",
                principalColumn: "Id",
                onDelete: ReferentialAction.Restrict);

Note

You need to delete UserRole first, then delete UserRoleRelationship

Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths even after removing the affected field completely

Based on my test, you can try the following steps to solve the problem.

First, please change your dbcontext class into the following code.

 public class dbContext : DbContext
{
    public dbContext() : base("name=MyContext") { }
    public DbSet<User> User { get; set; }
    public DbSet<Rating> Rating { get; set; }
    public DbSet<GameImage> GameImage { get; set; }
    public DbSet<Game> Game { get; set; }
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {

    }

}

Second, please delete all the tables the database.

Third, please try the following command in your package console.

PM> Update-Database -Force

Finally, you can see the new tables in the databse.

How can these constraints may cause cycles or multiple cascade paths?

The issue is due to multiple cascading paths from Author (Grand Parent) to BookRelease (Grand Child). There are two cascading paths:

1. Author -> Book -> BookRelease
2. Author -> Nickname -> BookRelease

This is discussed in detail in the post in MSSQLTips

So, the way to handle this is:

1. Disable the ON DELETE CASCAE and choose NOACTION as the foreign key creation.

2. Create INSTEAD OF DELETE TRIGGERS in Author(GrandParent), Book(Child1), Nickname(Child2) tables to handle the deletion of parent keys in the child tables.

• Grand Parent deletion : Delete in GrandChild, followed by Child1,
  followed by Child2,
• Child1 deletion: Delete in GrandChild, followed
  by Child1
• Child2 deletion: Delete in GrandChild, followed by Child2

EF Core - may cause cycles or multiple cascade paths

The error already says what you need to do. Specify what it must do when there is an action. You should add the .OnDelete() method to each foreign key definition.

modelBuilder.Entity<Tenant>()
                .HasOne<User>(s => s.User)
                .WithMany(ta => ta.Tenants)
                .HasForeignKey(u => u.UserId)
                .OnDelete(DeleteBehavior.Restrict);

For further information please read https://www.learnentityframeworkcore.com/configuration/fluent-api/ondelete-method

SQL Server Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

The Foreign key constraint may cause cycles or multiple cascade paths? question linked to by @Martin-Brown suggests using triggers instead of cascading foreign keys. Here's an example of using an INSTEAD OF DELETE trigger to do what (I think) you want to do.

-- Create tables
create table dbo.T_User (
    ID int identity not null primary key,
    Name varchar(100) not null
)

create table dbo.T_Order (
    ID int identity not null primary key,
    FK_UserActionOwnerID int not null,
    FK_UserActionReceiverID int not null
)
go

-- Create foreign keys
alter table dbo.T_Order add constraint FK_T_Order_T_Users1 FOREIGN KEY (FK_UserActionOwnerID) REFERENCES dbo.T_User (ID) 
alter table dbo.T_Order add constraint FK_T_Order_T_Users2 FOREIGN KEY (FK_UserActionReceiverID) REFERENCES dbo.T_User (ID) 
go

-- Create trigger
create trigger tr_T_User_Delete on dbo.T_User instead of delete as
begin

    if (@@rowcount = 0) return

    delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_UserActionOwnerID

    delete o from dbo.T_Order o inner join deleted d on d.ID = o.FK_UserActionReceiverID

    delete u from dbo.T_User u inner join deleted d on d.ID = u.ID  

end
go

-- Demo
insert dbo.T_User (Name) values ('Peter'), ('Paul') -- Assume identity ID 1 and 2

insert dbo.T_Order (FK_UserActionOwnerID, FK_UserActionReceiverID) values (1, 1), (1, 2), (2, 2)

select * from dbo.T_Order

delete from dbo.T_User where ID = 1

select * from dbo.T_Order

You can use INSTEAD OF UPDATE triggers in the same way but you might want to have a think about whether it makes sense for IDs to be updated - I wouldn't normally expect this.

---

Related Topics