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?

Answer 1

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.

Answer 2

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.