Introducing 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

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

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

All relationships in your model are required because all foreign key properties (CountryId, RegionId, CityId) are not nullable. For required one-to-many relationships EF will enable cascading delete by convention.

Country and Region have multiple delete paths to the Store table, for example if you delete a Country the related Stores can be deleted via three different cascading paths (which is not allowed with SQL Server):

• Country -> Store
• Country -> Region -> Store
• Country -> Region -> City -> Store

You must avoid such ambiguous delete paths by either disabling cascading delete using Fluent API or by defining some of the relationships as optional (with a nullable foreign key Guid?).

Or remove the Stores collections (and the inverse references and FK properties) from all entities except City. To me those collections look redundant because you can find all stores in a Country by navigating through the Regions.Cities.Stores collections.

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

Avoiding 'Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths'

It is unfortunate default behavior of EF code first to crate FK with on delete cascade. So while defining relations you need to simply change this setting:

//in context
protected override void OnModelCreating(DbModelBuilder modelBuilder) { 
    modelBuilder.Entity<Project>()
        .HasRequired<User>(s => s.User)
        .WithMany()
        .WillCascadeOnDelete(false);

Also there might be a convention for it in modelBuilder.Conventions if you want to just change behavior for all FK.

---

Related Topics