Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. - How?

Question

This issue can be replicated easily, but I do not know the correct way to resolve it.

For example, you have a Team class and a Game class. Each Game has two Teams. When using standard OOTB EF naming conventions, you will run into the following error when running dotnet ef database update (dotnet ef migrations add will run without error).

Classes:

public class Team
{
    [Required]
    public int TeamID { get; set; }
    public string TeamName { get; set; }
}

public class Game
{
    [Required]
    public int GameID { get; set; }
    public int Team1ID { get; set; }
    public Team Team1 { get; set; }
    public int Team2ID { get; set; }
    public Team Team2 { get; set; }
}

Make sure to add the two classes into your DbContext:

public virtual DbSet<Team> Teams { get; set; }
public virtual DbSet<Game> Games { get; set; }

The error I receive is:

Introducing FOREIGN KEY constraint 'FK_Games_Teams_Team2ID' on table 'Games' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I have previously worked around this issue by making both Team1ID and Team2ID nullable. But, that is obviously not the appropriate solution. A Game cannot exist without having exactly two Teams (in this scenario... let's say it's a game of soccer). Also, a Team should not be able to be deleted if it's participating (or participated) in at least one Game.

What is the appropriate way to resolve this issue? And if it is specifying ON DELETE NOT ACTION or ON UPDATE NO ACTION, or modifying other FOREIGN KEY constraints, how do you do so?

score 30 · Accepted Answer · edited Aug 08 '20 at 09:18

In EF Core, the cascading behavior of a relationship is configured through OnDelete relationship Fluent API (by default it is Cascade for required relationships like yours).

The tricky part is how to get access to that API, since there is no direct way (e.g. something like modelBuilder.Relation<TPrincipal, TDependent>()... would have been nice to have, but such API does not exist), so at minimum you need to start with entity type builder, followed by proper Has{One|Many} / With{One|Many} pair. By proper I mean passing the corresponding navigation property when exists. Failing to do so would lead to unexpected additional relationships / FKs since EF Core will map the unmapped navigation properties to default conventional relationships / FKs.

In your case it would be like this:

modelBuilder.Entity<Game>()
    .HasOne(e => e.Team1)
    .WithMany();

modelBuilder.Entity<Game>()
    .HasOne(e => e.Team2)
    .WithMany();

Now you can configure the cascade behavior, FK property / constraint names etc.

In this particular case, just insert .OnDelete(DeleteBehavior.Restrict) for both relationships and you are done:

modelBuilder.Entity<Game>()
    .HasOne(e => e.Team1)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict); // <--

modelBuilder.Entity<Game>()
    .HasOne(e => e.Team2)
    .WithMany()
    .OnDelete(DeleteBehavior.Restrict); // <--

For more info, see Relationships and EF Core API Reference sections of the documentation.

Perfect, worked like a charm! I also see DeleteBehavior.NoAction. I will research all these options. Thanks again! — Cardi DeMonaco Jr, Aug 10 '20 at 12:38

score 2 · Answer 2 · answered Aug 07 '20 at 18:29

On your DbContext class, you need to add:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Game>(entity =>
            {
                entity.Property(e => e.GameId)
                    .HasColumnName("GameID");

                entity.Property(e => e.Team1Id).HasColumnName("Team1ID");

                entity.Property(e => e.Team2Id).HasColumnName("Team2ID");

                entity.HasOne(d => d.Team1)
                    .WithMany(p => p.GameTeam1)
                    .HasForeignKey(d => d.Team1Id)
                    .OnDelete(DeleteBehavior.ClientNoAction)
                    .HasConstraintName("FK_Games_Teams_Team1ID");

                entity.HasOne(d => d.Team2)
                    .WithMany(p => p.GameTeam2)
                    .HasForeignKey(d => d.Team2Id)
                    .OnDelete(DeleteBehavior.ClientNoAction)
                    .HasConstraintName("FK_Games_Teams_Team2ID");
            });

            modelBuilder.Entity<Team>(entity =>
            {
                entity.Property(e => e.TeamId)
                    .HasColumnName("TeamID")
                    .ValueGeneratedNever();

                entity.Property(e => e.TeamName)
                    .IsRequired();
            });

            OnModelCreatingPartial(modelBuilder);
        }

This whole thing is not going to represent exactly what you need, but that is how you would set the ondelete and onupdate behaviors.

Thanks for the quick response! What is p.GameTeam1 and p.GameTeam2? Those properties are not part of the Team object. — Cardi DeMonaco Jr, Aug 07 '20 at 18:39

score 2 · Answer 3 · answered Apr 07 '23 at 15:06

Or set it globally for all Entities:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    if (modelBuilder == null)
        throw new ArgumentNullException("modelBuilder");

    // for the other conventions, we do a metadata model loop
    foreach (var entityType in modelBuilder.Model.GetEntityTypes())
    {
        // equivalent of modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
        entityType.SetTableName(entityType.DisplayName());

        // equivalent of modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        entityType.GetForeignKeys()
            .Where(fk => !fk.IsOwnership && fk.DeleteBehavior == DeleteBehavior.Cascade)
            .ToList()
            .ForEach(fk => fk.DeleteBehavior = DeleteBehavior.Restrict);
    }

    base.OnModelCreating(modelBuilder);
}

Solution by Dennes Torres, source: https://www.red-gate.com/simple-talk/blogs/change-delete-behavior-and-more-on-ef-core/

score 0 · Answer 4 · answered Aug 19 '23 at 09:38

in my case it was a many-to-many relationship and it was driving me crazy, tried a few thing and the only thing that worked is to set the DeleteBehavior to ClientCascade

public class Invoices
{
    public int Id { get; set; }
    //(few other parameters)
    public ICollection<Products> products { get; set; }
}

public class Products
{
    public int Id { get; set; }
    //(few other parameters)
    public ICollection<Invoices> invoices { get; set; }
}

public class InvoiceProduct
{
    public int InvoiceId { get; set; }
    public int ProductId { get; set; }
}

these are my classes in it's most basic form and it was giving me long error about ON DELETE CASCADE to fix it I added the code below inside OnModelCreating override method of DbContext :

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {

        modelBuilder.Entity<Products>()
            .HasMany(e => e.invoices)
            .WithMany(e => e.products)
            .UsingEntity<InvoiceProduct>(
                r => r.HasOne<Invoices>().WithMany().HasForeignKey(e => e.InvoiceId).OnDelete(DeleteBehavior.ClientCascade),
                l => l.HasOne<Products>().WithMany().HasForeignKey(e => e.ProductId).OnDelete(DeleteBehavior.ClientCascade));
    }

the .OnDelete(DeleteBehavior.ClientCascade) part solved the error

Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. - How?

4 Answers4

Linked