FOREIGN KEY constraint may cause cycles or multiple cascade paths [error message]

Question

I add provincesModel's pr_id as a foreign Key to clinicsModel. So the visual studio display me this error message

Introducing FOREIGN KEY constraint 'FK_dbo.ClinicsModels_dbo.ProvincesModels_pr_id' on table 'ClinicsModels' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I try to solve the issue by browsing on web to find some solution but I can't get how to solve this. So I remove the foreign key relation of pr_id from clinicsModel and run the project but still visual studio shows me the same error message. I also recreate the ClinicsModel and try to update database using Update-Database -Verebose Migrations using package manager console but still it shows the same error message.

Here's my code:

for ProvinceModel

namespace finalFyp.Models
{
    public class ProvincesModel
    {
        [Key]
        public int pr_id { get; set; }
        public string pr_name { get; set; }
        public ICollection<CitiesModel> cities { get; set; }
    }
}

ClinicsModel:

 public class ClinicsModel
    {
        [Key]
        public int clinic_id { get; set; }
        public string clinic_name { get; set; }
        public string clinic_address { get; set; }
        //Forigen Keys
        public int ct_id { get; set; }
        public CitiesModel city { get; set; }
    }
}

As the error occurred when I redirecting to http://localhost:3110/Doctors/index Here's the snapshot of of error message.

DoctorsModel:

public class DoctorsModel
{
    [Key]
    public int d_id { get; set; }
    public string d_name { get; set; }
    public string contact { get; set; }
    public string cnic { get; set; }
    public string email { get; set; }
    public string gender { get; set; }
    //Forigen Key
    public ICollection<DocExperiencesModel> experiences { get; set; }
    public ICollection<DocSpecialization> specializations { get; set; }
    public ICollection<QualificationsModel> qualifications { get; set; }
    public ICollection<DoctorProfileModel> profiles { get; set; }
}

For ease of understanding Here's the schema of my database.

Please guide me what I suppose to do? I will be very thankful to him/her.

score 0 · Answer 1 · edited May 23 '17 at 12:00

From first glance, the error occured when pr_id added as foreign key to ClinicsModel because ProvincesModel has one-to-many relationship against ClinicsModel table which involves pr_id primary key field. Since all foreign keys which referencing pr_id are not nullable, all one-to-many relationships where ProvincesModel involved are having cascade delete enabled by default. Hence, it means when a ClinicsModel entity data is deleted, it will have 2 cascade delete paths: through CitiesModel and directly to ProvincesModel as shown in image below.

As a result, it establishing circular relationship with more than one cascade delete path from ClinicsModel to ProvincesModel which causes error 1785 in SQL Server.

To resolve relationships problem, pr_id foreign keys should declared as Nullable<int>:

public int? pr_id { get; set; }

Likewise, if ct_id (and other int foreign key properties which subjected to possible circular relationships) also returning same error, declare them with same way as above:

public int? ct_id { get; set; }

NB: If Fluent API (and Code First) is being used, try adding these lines below:

// taken from /a/20915232
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
}

// modified from /a/17127512
// used on all entities with circular relationships
var builder = new DbModelBuilder();
builder.Entity<CitiesModel>()
       .HasRequired(x => x.ct_id)
       .WithMany()
       .WillCascadeOnDelete(false);

builder.Entity<ProvincesModel>()
       .HasRequired(x => x.pr_id)
       .WithMany()
       .WillCascadeOnDelete(false);

Similar issues:

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths

Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths

FOREIGN KEY constraint may cause cycles or multiple cascade paths [error message]

1 Answers1