CODEWITHSUNDEEP
entity-framework.net-coreentity-framework-coreentity-framework-migrations
Coder Guy
Coder Guy

Reputation: 325

Entity framework: Cascading foreign key cannot be created where the referencing column is an identity column. Could not create constraint or index

I am having an issue trying to create a one to one relationship to a pivot table. These are my models

    public class StudentCourse
    {
        public int Id { get; set; }
        public int StudentId { get; set; }
        public virtual Student Student { get; set; }
        public int CourseId { get; set; }
        public virtual Course Course { get; set; }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public virtual IList<StudentCourse> StudentCourses { get; set; }
    }

    public class Course
    {
        public int Id { get; set; }
        public string CourseName { get; set; }
        public string Description { get; set; }
        public virtual IList<StudentCourse> StudentCourses { get; set; }
    }

    public class ExamResult
    {
        public int Id { get; set; }
        public int Marks { get; set; }
        public int TotalMarks { get; set; }
        public virtual StudentCourse StudentCourse { get; set; }
    }

This is the model builder config where I have defined my many to many relationship between Student and Course

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<StudentCourse>().HasKey(sc => new { sc.StudentId, sc.CourseId });
                modelBuilder.Entity<StudentCourse>().HasAlternateKey(e => e.Id);
                modelBuilder.Entity<StudentCourse>().Property(e => e.Id).ValueGeneratedOnAdd();

                modelBuilder.Entity<StudentCourse>()
                    .HasOne(sc => sc.Student)
                    .WithMany(s => s.StudentCourses)
                    .HasForeignKey(sc => sc.StudentId)
                    .OnDelete(DeleteBehavior.Cascade);

                modelBuilder.Entity<StudentCourse>()
                    .HasOne(sc => sc.Course)
                    .WithMany(s => s.StudentCourses)
                    .HasForeignKey(sc => sc.CourseId)
                    .OnDelete(DeleteBehavior.Cascade);
            }

And this is the error message I am getting when I run the migrations

Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [StudentCourses] (
    [StudentId] int NOT NULL,
    [CourseId] int NOT NULL,
    [Id] int NOT NULL IDENTITY,
    CONSTRAINT [PK_StudentCourses] PRIMARY KEY ([StudentId], [CourseId]),
    CONSTRAINT [AK_StudentCourses_Id] UNIQUE ([Id]),
    CONSTRAINT [FK_StudentCourses_Courses_CourseId] FOREIGN KEY ([CourseId]) REFERENCES [Courses] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_StudentCourses_ExamResults_Id] FOREIGN KEY ([Id]) REFERENCES [ExamResults] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_StudentCourses_Students_StudentId] FOREIGN KEY ([StudentId]) REFERENCES [Students] ([Id]) ON DELETE CASCADE
);

Cascading foreign key 'FK_StudentCourses_ExamResults_Id' cannot be created where the referencing column 'StudentCourses.Id' is an identity column.
Could not create constraint or index. See previous errors.

Essentially I am trying to point to a StudentCourse record from the ExamResult table, but for some reason it's failing. Does anyone have any idea?

Upvotes: 2

Views: 3671

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205719

This is an EF Core issue/bug which seems to be fixed in EF Core 3.0. The alternate key Id of StudentCourse somehow is confusing the pre EF Core 3.0 relationship conventions and instead of intended many-to-one relationship from ExamResult to StudentCourse it wrongly assumes one-to-one relationship with ExamResult being the principal and StudentCourse being the dependent, hence the lack of FK in ExamResult and wrong FK in StudentCourse.

The solution is to explicitly configure the desired relationship via fluent API:

modelBuilder.Entity<ExamResult>()
    .HasOne(e => e.StudentCourse)
    .WithMany();

Note that this will create an optional composite FK in ExamResult referencing the composite PK of StudentCourse. If you want to relate the by the alternate key of the StudentCourse, you should configure that with HasPrincipalKey API:

modelBuilder.Entity<ExamResult>()
    .HasOne(e => e.StudentCourse)
    .WithMany()
    .HasPrincipalKey(sc => sc.Id);

Upvotes: 2

Related Questions