Failed executing DbCommand-Introducing FOREIGN KEY constraint

Question

Here showing following error when going to update-database, through dotnet-core migration.

        Failed executing DbCommand (48ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
        CREATE TABLE [DepartmentSchool] (
        [DepartmentID] uniqueidentifier NOT NULL,
        [SchoolsId] uniqueidentifier NOT NULL,
        [Id] uniqueidentifier NOT NULL,
        CONSTRAINT [PK_DepartmentSchool] PRIMARY KEY ([SchoolsId], [DepartmentID]),
        CONSTRAINT [FK_DepartmentSchool_Department_DepartmentID] FOREIGN KEY ([DepartmentID]) REFERENCES [Department] ([ID]) ON DELETE CASCADE,
        CONSTRAINT [FK_DepartmentSchool_School_SchoolsId] FOREIGN KEY ([SchoolsId]) REFERENCES [School] ([ID]) ON DELETE CASCADE
    );

Below is the entity relation class are:

School Class(First Table):

public partial class Schools
{
    public Guid ID { get; set; }
    public string Name { get; set; }

    public Guid? CountryId { get; set; }
    public Country Country { get; set; }

    public ICollection<DepartmentSchool> DepartmentSchools { get; set; } 
}

Department Class(Second Table):

public partial class Department
{
    public Guid ID { get; set; }
    public string Title { get; set; }

    public DateTime CreatedAt { get; set; }

    public ICollection<DepartmentSchool> DepartmentSchools { get; set; } 
}

DepartmentSchool Class (Middle table)

public class DepartmentSchool
    {
        public Guid Id { get; set; }

        public Guid DepartmentID { get; set; }
        public Department Department { get; set; }

        public Guid SchoolsId { get; set; }
        public Schools Schools { get; set; }
    }

and in the modulbulider it is relation is define here:

 //Many to many relationship between School and Depatment
        modelBuilder.Entity<DepartmentSchool>()
            .HasKey(ds => new { ds.SchoolsId, ds.DepartmentID });
        modelBuilder.Entity<DepartmentSchool>()
            .HasOne(ds => ds.Department)
            .WithMany(d => d.DepartmentSchools)
            .HasForeignKey(ds => ds.DepartmentID);
        modelBuilder.Entity<DepartmentSchool>()
            .HasOne(ds => ds.Schools)
            .WithMany(d => d.DepartmentSchools)
            .HasForeignKey(ds => ds.SchoolsId);

Introducing FOREIGN KEY constraint 'FK_DepartmentSchool_School_SchoolsId' on table 'DepartmentSchool' 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. — Vipin Jha, Jan 06 '20 at 09:21
The above problem occurs during the migration in dotnet core, when update database in the SQL Server — Vipin Jha, Jan 06 '20 at 09:30

Amin Golmahalleh · Accepted Answer · 2022-11-01T17:49:04.200

3

Disable cascading delete by allowing null values in the references.

.OnDelete(DeleteBehavior.Restrict);

About Cascade Delete In Asp Core

For example:

modelBuilder.Entity<Invoice>()
            .HasOne(i => i.Customer)
            .WithMany(c => c.Invoices)
            .OnDelete(DeleteBehavior.Restrict);

Help link about reason error

edited Nov 01 '22 at 17:49

answered Jan 06 '20 at 10:31

Amin Golmahalleh

3,585
2
23
36

You can not use IsRequired(). This Code Just an example.Is your problem solved? – Amin Golmahalleh Jan 06 '20 at 14:28

score 0 · Answer 2 · edited Nov 01 '22 at 17:52

I added the code according to the accepted answer and it is working:

 modelBuilder.Entity<DepartmentSchool>()
            .HasKey(ds => new { ds.SchoolsId, ds.DepartmentID });
        modelBuilder.Entity<DepartmentSchool>()
            .HasOne(ds => ds.Department)
            .WithMany(d => d.DepartmentSchools)
            .OnDelete(DeleteBehavior.Restrict)
            .HasForeignKey(ds => ds.DepartmentID);
        modelBuilder.Entity<DepartmentSchool>()
            .HasOne(ds => ds.Schools)
            .WithMany(d => d.DepartmentSchools)
            .OnDelete(DeleteBehavior.Restrict)
            .HasForeignKey(ds => ds.SchoolsId);

Failed executing DbCommand-Introducing FOREIGN KEY constraint

2 Answers2