System.Data.SqlClient.SqlException: 'Introducing FOREIGN KEY constraint

c# entity-framework entity-framework-core

Question

I'm using Entity Framework Core with Code First approach but receive following error when trying to start application:

System.Data.SqlClient.SqlException: 'Introducing FOREIGN KEY constraint 'FK_Grades_Students_StudentId' on table 'Grades' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.'

My entities:

public class Student
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Password { get; set; }

    public string Email { get; set; }

    public Group Group { get; set; }

    public IEnumerable<Subject> Subjects { get; set; } = new List<Subject>();


}

public class Subject
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public string Name { get; set; }

    public IEnumerable<Grade> Grades { get; set; } = new List<Grade>();

    [ForeignKey("StudentId")]

    public Student Student { get; set; }

    public Guid StudentId { get; set; }

}

public class Grade
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public double Value { get; set; }

    public DateTime DateOfGettingGrade { get; set; }

    [ForeignKey("SubjectId")]

    public Subject Subject { get; set; }

    public Guid SubjectId { get; set; }

    [ForeignKey("StudentId")]

    public Student Student { get; set; }

    public Guid StudentId { get; set; }
}


modelBuilder.Entity("GradeBook.Core.Models.Grade", b =>
            {
                b.HasOne("GradeBook.Core.Models.Student", "Student")
                    .WithMany()
                    .HasForeignKey("StudentId")
                    .OnDelete(DeleteBehavior.Cascade);

                b.HasOne("GradeBook.Core.Models.Subject", "Subject")
                    .WithMany("Grades")
                    .HasForeignKey("SubjectId")
                    .OnDelete(DeleteBehavior.Cascade);
            });
1
0
2/28/2018 7:16:17 PM

Accepted Answer

Because Stage is required, all one-to-many relationships where Stage is involved will have cascading delete enabled by default. It means, if you delete a Stage entity

  • the delete will cascade directly to Side
  • the delete will cascade directly to Card and because Card and Side have a required one-to-many relationship with cascading delete enabled by default again it will then cascade from Card to Side

So, you have two cascading delete paths from Stage to Side - which causes the exception.

You must either make the Stage optional in at least one of the entities (i.e. remove the [Required] attribute from the Stage properties) or disable cascading delete with Fluent API (not possible with data annotations):

modelBuilder.Entity<Card>()
    .HasRequired(c => c.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Side>()
    .HasRequired(s => s.Stage)
    .WithMany()
    .WillCascadeOnDelete(false);
364
6/5/2018 3:10:52 PM

Popular Answer

I had a table that had a circular relationship with others and i was getting the same error. Turns out it is about the foreign key which was not nullable. If key is not nullable related object must be deleted and circular relations doesnt allow that. So use nullable foreign key.

[ForeignKey("StageId")]
public virtual Stage Stage { get; set; }
public int? StageId { get; set; }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow