C# FrameworkCore and MySQL: Cannot delete or update a parent row: a foreign key constraint fails

Question

Need some help here... I am copying code directly from one of my school classes, and they show the result in their code, but each time I run it, I get the following exception error:

Exception has occurred: CLR/Microsoft.EntityFrameworkCore.DbUpdateException An unhandled exception of type 'Microsoft.EntityFrameworkCore.DbUpdateException' occurred in Microsoft.EntityFrameworkCore.dll: 'An error occurred while updating the entries. See the inner exception for details.' Inner exceptions found, see $exception in variables window for more details. Innermost exception MySql.Data.MySqlClient.MySqlException : Cannot delete or update a parent row: a foreign key constraint fails (movies.film_actor, CONSTRAINT film_actor_actor FOREIGN KEY (ActorId) REFERENCES actor (actorid)) at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() at MySqlConnector.Core.ResultSet.d__1.MoveNext() in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 44

Here is the method being called:

internal static void DeleteItem()
{
    Console.WriteLine("Delete an Actor");

    Console.WriteLine("Enter an Actor ID");
    var actorId = Console.ReadLine().ToInt();

    var actor = MoviesContext.Instance.Actors.SingleOrDefault(a => a.ActorId == actorId);
    if (actor == null) {
        Console.WriteLine($"Actor with id {actorId} not found.");
    }
    else {
        Console.WriteLine("Existing Actors");
        WriteActors();

        MoviesContext.Instance.Actors.Remove(actor);

        MoviesContext.Instance.SaveChanges();

        Console.WriteLine("With actor removed.");
        WriteActors();
    }
}

I have checked my code over and over again and it matches the instructor's code perfectly. Unfortunately, no one is addressing my question through the college Blackboard, so I brought my question here.

The framework reverse-engineered the database to build all of the scaffolding for the code. The program is throwing this exception when the MySQL statement for SaveChanges() is sent.

However, it must be from this table, because SaveChanges() is used in an earlier part of the code for something else, and it doesn't throw any errors.

Ideas? This is driving me Looney! Thanks. :)

Using:

C# 
ConsoleTables 
EntityFrameworkCore 
 + .Design 
 + .Tools         // Used as a DotNetCliToolReference 
 + .Tools.DotNet  // Used as a DotNetCliToolReference 
 + .MySql 
 + .MySql.Design 

I don't know anything about constraints, but here is the section of code I found that the framework generated that may be a contributor:

modelBuilder.Entity<FilmActor>(entity =>
            {
                entity.HasKey(e => new { e.FilmId, e.ActorId });

                entity.ToTable("film_actor");

                entity.HasIndex(e => e.ActorId)
                    .HasName("film_actor_actor_idx");

                entity.Property(e => e.FilmId).HasColumnType("int(11)");

                entity.Property(e => e.ActorId).HasColumnType("int(11)");

                entity.HasOne(d => d.Actor)
                    .WithMany(p => p.FilmActor)
                    .HasForeignKey(d => d.ActorId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("film_actor_actor");

                entity.HasOne(d => d.Film)
                    .WithMany(p => p.FilmActor)
                    .HasForeignKey(d => d.FilmId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("film_actor_film");
            });

Perhaps I'm not using this model correctly somewhere?

RESOLUTION

After much help below I was able to accomplish the fix by simply creating a loop that went through the associated table film_actor and deleted any rows with the ActorId that was related to the actor being deleted. Here is the quick loop I put in before the SaveChanges() method gets executed:

foreach (var tatertot in MoviesContext.Instance.FilmActors) {
     if(tatertot.ActorId == actorId) {
          MoviesContext.Instance.FilmActors.Remove(tatertot);
     }
}

I'd like to thank everyone for their help. I'm sure I'll have more as this class goes on! Thank you. :)

Answer 1

This is the vital piece of information:

Cannot delete or update a parent row: a foreign key constraint fails (movies.film_actor, CONSTRAINT film_actor_actor FOREIGN KEY (ActorId) REFERENCES actor (actorid).

If I'm reading the details correctly, what that means is that there is a value in the column film_actor_actor in movies.film_actor that points to the id (in the column ActorId) of the actor you are trying to delete.

The foreign key constraint means you can't delete the actor before removing the film_actor pointing to it first, because if you did, the film_actor would be pointing to a non-existing row, which would render your database inconsistent.

Solution: You need to delete any and all references to an actor row before you can delete the actor row itself.