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);
});
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
Side
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);
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; }