Entity Framework Core 5 - Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths

Question

I am trying to implement Entity Framework core 5 and am also new to it. below are three models that I am trying to implement.

before posting a new question, I checked the following answers but couldn't understand the card example. Maybe my problem listed below will help anyone else like me today to understand it better.

My models are given below :

[Table("Clinics")]
public class Clinic
{
    public int ClinicID { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

    public string Address { get; set; }

    public List<Doctor> DoctorsAvailable { get; set; } = new List<Doctor>();
}


[Table("Doctors")]
public class Doctor
{
    public int ID { get; set; }

    public string Name { get; set; }

    public DoctorsSpecilization Specilization { get; set; }

    public string PhoneNumber { get; set; }

    public string Email { get; set; }

    public List<Clinic> ClinicsAvailableAt { get; set; } = new List<Clinic>();
}

 [Table("Patients")]
public class Patient
{
    public int PatientID { get; set; }

    public string Name { get; set; }

    public string Address { get; set; }

    public string PhoneNumber { get; set; }

    public string Email { get; set; }

    public int DoctorID { get; set; }

    public Doctor Doctor { get; set; }
}


[Table("Consultations")]
public class Consultation
{
    public int ID { get; set; }

    public int ClinicID { get; set; }

    public int DoctorID { get; set; }

    public int PatientID { get; set; }

    public Clinic Clinic { get; set; }

    [ForeignKey("DoctorID")]
    public Doctor Doctor { get; set; }

    [ForeignKey("PatientID")]
    public Patient Patient { get; set; }

    public DateTime StartTime { get; set; }

    public DateTime EndTime { get; set; }

}

The problem is the navigation properties of the Doctor and Patient in the Consultation model. When I try to "update-database" it fails with

Introducing FOREIGN KEY constraint 'FK_Consultations_Patients_PatientID' on table 'Consultations' 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.

However, if the navigation properties are removed it works fine. You may ask why I need to have those navigation properties. It's for the sake of displaying relevant information on the view.

Any help in explaining or commenting about the concept would be very much appreciated.

Thank you.

There is no visible cycle in the shown model. Please, (1) provide the third involved entity (`Patient`) (2) are you using EF Core 5, thus having implicit many-to-many linking table between `Clinic` and `Doctor`? — Ivan Stoev, Jul 31 '21 at 09:45

Ivan Stoev · Accepted Answer · 2021-07-31T17:49:37.333

Here are the cascade delete paths in the shown model

Clinic -> Consultation

Doctor -> Consultation

Patient -> Consultation

Doctor -> Patient -> Consultation

The problem (multiple cascade paths) are the last two. As you can see, when deleting a Doctor, the linked Consultation records can be deleted either directly or view linked Patient records. Because of that possibility, some databases (mainly SqlServer) reject cascade delete options and require you to turn of it for at least one of the relationships forming the cycle and handle the deletion manually or via trigger.

So normally that's what you should do when such cycle exists.

But here looks like something is wrong with the model. Either Patient should not be linked to a single Doctor, but to many via linking table and removing Patient.Doctor navigation property (thus the associated FK relationship), thus naturally breaking the multiple cascade paths, i.e. deleting Doctor deletes just links to clinics and patients, but not clinics and patients themselves.

Or, if you want to keep Patient to single Doctor relationship, then Consultation.Doctor (and associated Consultation.DoctorId FK and relationship) is redundant - the doctor of the consultation can be obtained via consultation.Patient.Doctor). So remove it and that will also solve the multiple cascade paths issue since there will be no more Doctor -> Consultation cascade delete link.

For clarity, the first suggested option requires the following model changes:


[Table("Clinics")]
public class Clinic // Unchanged
{
    public int ClinicID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string Address { get; set; }
    public List<Doctor> DoctorsAvailable { get; set; } = new List<Doctor>();
}

[Table("Doctors")]
public class Doctor
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public List<Clinic> ClinicsAvailableAt { get; set; } = new List<Clinic>();
    public ICollection<Patient> Patients { get; set; } // <-- added
}

[Table("Patients")]
public class Patient
{
    public int PatientID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    //public int DoctorID { get; set; } <-- removed
    //public Doctor Doctor { get; set; } <-- removed
    public ICollection<Doctor> Doctors { get; set; } // <-- added
}


[Table("Consultations")]
public class Consultation // Unchanged
{
    public int ID { get; set; }
    public int ClinicID { get; set; }
    public int DoctorID { get; set; }
    public int PatientID { get; set; }
    public Clinic Clinic { get; set; }
    [ForeignKey("DoctorID")]
    public Doctor Doctor { get; set; }
    [ForeignKey("PatientID")]
    public Patient Patient { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

and option 2:


[Table("Clinics")]
public class Clinic // Unchanged
{
    public int ClinicID { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string Address { get; set; }
    public List<Doctor> DoctorsAvailable { get; set; } = new List<Doctor>();
}

[Table("Doctors")]
public class Doctor // Unchanged
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public List<Clinic> ClinicsAvailableAt { get; set; } = new List<Clinic>();
}

[Table("Patients")]
public class Patient // Unchanged
{
    public int PatientID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public int DoctorID { get; set; }
    public Doctor Doctor { get; set; }
}


[Table("Consultations")]
public class Consultation
{
    public int ID { get; set; }
    public int ClinicID { get; set; }
    //public int DoctorID { get; set; } <-- removed
    public int PatientID { get; set; }
    public Clinic Clinic { get; set; }
    //[ForeignKey("DoctorID")]
    //public Doctor Doctor { get; set; } <-- removed
    [ForeignKey("PatientID")]
    public Patient Patient { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime EndTime { get; set; }
}

Thank you Ivan. After i removed the doctor navigation property and DoctorID property in the Consultation model as you had suggesed.it now shows error as Introducing FOREIGN KEY constraint 'FK_Consultations_Patients_PatientID' on table 'Consultations' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION. Also, I suppose that client is a typo in your answer and you actually meant the consultation model? — Faheem Rasheed, Jul 31 '21 at 17:12
did both things as you had mentioned. it fails. Introducing FOREIGN KEY constraint 'FK_Consultations_Patients_PatientID' on table 'Consultations' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. — Faheem Rasheed, Jul 31 '21 at 17:27
Tried removing `Doctor` properties from `Consultation`, generated migration, the update-database, and it worked w/o issue. Do you have something else not shown? — Ivan Stoev, Jul 31 '21 at 17:29
not really. same here. generated the migration after removing the doctor properties and made the single doctor property in Patient as a list of doctor. update database fails. — Faheem Rasheed, Jul 31 '21 at 17:31
The only additional mod I did was to comment `public DoctorsSpecilization Specilization { get; set; }` since have no idea what it is. — Ivan Stoev, Jul 31 '21 at 17:32
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235490/discussion-between-faheem-rasheed-and-ivan-stoev). — Faheem Rasheed, Jul 31 '21 at 17:35
But they are not the same. You have `public List DoctorsConsulted { get; set; }` in `Patient`, so it's some sort of mixture. See updated answer for the exact two configurations - both tested and working. And be very careful with any **reference** and **collection** navigation property as they always introduce some relationship, even though you don't declare specific inverse property or FK at the other side. — Ivan Stoev, Jul 31 '21 at 17:54

Entity Framework Core 5 - Introducing FOREIGN KEY constraint on table may cause cycles or multiple cascade paths

1 Answers1