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.

1. Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?
2. Foreign key constraint may cause cycles or multiple cascade paths?

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.

Answer 1

Here are the cascade delete paths in the shown model

1. Clinic -> Consultation
2. Doctor -> Consultation
3. Patient -> Consultation
4. 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; }
}