Enforce ON DELETE NO ACTION in entity framework core

Suggested Videos
Part 86 - Delete identity user in asp.net core | Text | Slides
Part 87 - ASP.NET Core delete confirmation | Text | Slides
Part 88 - Delete identity role in asp.net core | Text | Slides

In this video we will discuss, how to enforce foreign key constraint referential integrity ON DELETE NO ACTION in entity framework core.

• Users are stored in AspNetUsers table
• Roles are stored in AspNetRoles table
• User and Role mapping data is stored in AspNetUserRoles table
• This table has just 2 columns : UserId & RoleId
• Both are foreign keys

Cascading referential integrity constraint

Cascading referential integrity constraint allows to define the actions Microsoft SQL Server should take when a user attempts to delete or update a key to which an existing foreign keys points.

We discussed foreign keys and cascading referential integrity constraint in detail in Part 5 of SQL Server tutorial.

Foreign key with Cascade DELETE

In Entity Framework Core, by default the foreign keys in AspNetUserRoles table have Cascade DELETE behaviour. This means, if a record in the parent table (AspNetRoles) is deleted, then the corresponding records in the child table (AspNetUserRoles ) are automatically be deleted.

Foreign key with NO ACTION ON DELETE

What if you want to customise this default behaviour. We do not want to allow a role to be deleted, if there are rows in the child table (AspNetUserRoles) which point to a role in the parent table (AspNetRoles).

To achieve this, modify foreign keys DeleteBehavior to Restrict. We do this in OnModelCreating() method of AppDbContext class

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    foreach (var foreignKey in modelBuilder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
    {
        foreignKey.DeleteBehavior = DeleteBehavior.Restrict;
    }
}

Build the solution. Add a new migration and update the database.

With this change, if you view the properties of the foreign key you will see ON DELETE is set to NO ACTION



At this point, an error will be thrown, if you try to delete a role from AspNetRoles table, for which there are child rows in AspNetUserRoles table and the DELETE action will be rolled back. You have to delete the CHILD rows before deleting the parent row.