If you’re working with SQL Server along with Entity Framework Core or EF Core ORM, you might be stuck with this problem.
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.
What is “multiple cascade paths” problem?
Take a look at figure 1. In this example we have 4 tables. Users, Blog, Posts and Comments. Arrows here are showing relations or mappings between entities/tables.
- A User can have a blog.
- A blog can have multiple posts.
- Each post can itself have multiple comments.
Now the issue here is that, from the Users table, we have 3 different ways to reach Comments table.
- User -> Blog -> Posts -> Comments
- User -> Posts -> Comments
- User -> Comments
Now if we remove a record from Users table. Microsoft SQL server can have multiple paths of deleting related entities. Which path should it take to remove all entities related to the record to maintain referential integrity?
To solve this issue, SQL server restricts to a single cascade path. This doesn’t mean that you’ll have single foreign key relation. Users table can still be mapped to Comments table using foreign keys and joins but it should specify an On Delete behavior.
In Entity Framework Core you can specify this kind of relations or mappings like this:
modelBuilder.Entity<Comment>() .HasOne(x => x.User) .WithMany(x => x.Comments) .OnDelete(DeleteBehavior.Restrict);
This will generate the following code in migrations:
migrationBuilder.AddForeignKey( name: "FK_Comments_Users_UserID", table: "Comments", column: "UserID", principalTable: "Users", principalColumn: "ID", onDelete: ReferentialAction.Restrict);
ReferentialAction.Restrict means that when this entity is removed, it will not remove its child or connected entities, it will restrict cascade path to itself.
Now if you run migration with update-database, it will not cause multiple cascade paths exception. SQL server will know that Users and Comments tables are connected but on delete of user record, it should not remove any comments record.
But When User will remove Blog and Post, that path will automatically cause Comments to get removed. So referential integrity will remain.