Change DeleteBehavior from Cascade to Restrict in EF Core code first

Question

I'm trying to change the DeleteBehavior from Cascade to Restrict I'm using EF core to make migrations.

This is how the migration looks that with the cascade deletebehavior

migrationBuilder.CreateTable(
            name: "ElementsPerStrip",
            columns: table => new
            {
                ......
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_ElementsPerStrip", x => x.Id);
                table.ForeignKey(
                    name: "FK_ElementsPerStrip_Strips_StripId",
                    column: x => x.StripId,
                    principalTable: "Strips",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            });

There have been a few migrations after this and now I need to revert ReferentialAction to Restrict

in this is how my Strip model looks like:

public class Strip
{
    public int Id { get; set; }
    public int ProjectId { get; set; }
    public int ScriptId { get; set; }
    public virtual Script Script { get; set; }
    public int SceneId { get; set; }        
    ...
    public virtual ICollection<ElementPerStrip> ElementPerStrip { get; set; }
}

and this is how my ElementPerStrip looks:

 public class ElementPerStrip
{
    public int Id { get; set; }
    ...
    public int StripId { get; set; }
    // public virtual Strip strip { get; set; } // issue is here
}

and in modelbuilder I've added this in an attempt to change the Deletebehavior to restrict:

builder.Entity<Strip>()
        .HasMany(c => c.ElementPerStrip)
        .WithOne()
        .HasForeignKey(c => c.StripId)
        .OnDelete(DeleteBehavior.Restrict);

the migration it creates looks correct to me:

  migrationBuilder.DropForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip");

        migrationBuilder.AddForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip",
            column: "StripId",
            principalTable: "Strips",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);

but when I run my application and try to delete a strip, I get following error:

The DELETE statement conflicted with the REFERENCE constraint "FK_ElementsPerStrip_Strips_StripId". The conflict occurred in database "MDb", table "dbo.ElementsPerStrip", column 'StripId'.

I can't remove the StripId column because I need the data for filtering purposes in the application. How can I resolve this issue that I disconnect both tables but keep the data but change the Deletebehaviour.

Answer 1

To change the delete behavior using an EF migration, you'll have to drop and recreate the foreign key.

In the Package Manager Console, create a new, empty migration with the Add-Migration command, then fill in the Up method like this:

        migrationBuilder.DropForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip");
        migrationBuilder.AddForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip",
            column: "StripId",
            principalTable: "Strips",
            principalColumn: "Id",
            onDelete: ReferentialAction.Restrict);

For completeness, do the opposite in the Down method:

        migrationBuilder.DropForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip");
        migrationBuilder.AddForeignKey(
            name: "FK_ElementsPerStrip_Strips_StripId",
            table: "ElementsPerStrip",
            column: "StripId",
            principalTable: "Strips",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);

Please note: ignore any advice to tinker with you DB schema through SSMS. The moment you do this, you have blown you migration strategy, the whole point of which is to keep you in control of your schema through the build/deploy pipeline, and always keep your schema in line with your code.

Answer 2

There are four available options in SQL Server Server 2005 and later as follows:

• No Action
• Cascade
• SET NULL
• SET Default

Here is summary of the effects for update and delete operations:

Steps of adding CONSTRAINT to FOREIGN KEY

Open New Query window

[SQL] adding CONSTRAINT

ALTER TABLE [dbo].[ToDoItem]
   ADD CONSTRAINT FK_Delete_NoAction
   FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]) ON DELETE No Action ON UPDATE No Action

Click green arrow to excute.

Result with No Action setting

When you change back to **Cascade** mode

ALTER TABLE [dbo].[ToDoItem]
   DROP CONSTRAINT [FK_Delete_NoAction]

ALTER TABLE [dbo].[ToDoItem]
   ADD CONSTRAINT FK_Delete_Cascade
   FOREIGN KEY ([UserId]) REFERENCES [dbo].[User] ([Id]) ON DELETE Cascade ON UPDATE Cascade

The ToDoItem will be deleted when user deleted.

If your DB is working on SQL Server Management Studio (SSMS), it will be more easier.

→ Right click the table design and go to Relationships and choose the foreign key on the left-side pane and in the right-side pane, expand the menu "INSERT and UPDATE specification" and select "Restrict" as Delete Rule.