SQL Server error 1785: 'FOREIGN KEY constraint may cause cycles or multiple cascade paths'

Introducing FOREIGN KEY constraint 'FK_Appointments_Patients' on table 'Appointments' may cause cycles or multiple cascade paths. (error 1785) means a delete in one table could reach another table along two different ON DELETE CASCADE routes, and SQL Server does not allow that. Keep one route cascading and declare the other foreign key with ON DELETE NO ACTION.

The message itself gives the fix: specify NO ACTION on the new key, or change one of the existing keys. Then delete the child rows yourself on the path that no longer cascades.

The error

ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_Patients
    FOREIGN KEY (PatientId) REFERENCES dbo.Patients (Id) ON DELETE CASCADE;

Msg 1785, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Introducing FOREIGN KEY constraint 'FK_Appointments_Patients' on table 'Appointments' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 2
Could not create constraint or index. See previous errors.

Why it happens

The schema is a common one. Doctors and patients both belong to a clinic, and both foreign keys to Clinics cascade. Appointments belong to a doctor, with a cascading key, and to a patient. Adding the patient key with ON DELETE CASCADE would mean that deleting a clinic reaches Appointments twice: once through Doctors and once through Patients. SQL Server refuses any design in which one delete can arrive at a table by more than one cascading path, or loop back to where it started, even when no data exists yet. It checks the declarations, not the rows. The follow-up error 1750 only says the constraint was not created.

An EF Core model with two cascading routes to one table asks SQL Server for the same constraints, so the same rule applies; the reproduction here is plain T-SQL.

The fix

ALTER TABLE dbo.Appointments ADD CONSTRAINT FK_Appointments_Patients
    FOREIGN KEY (PatientId) REFERENCES dbo.Patients (Id) ON DELETE NO ACTION;

The constraint was created, and sys.foreign_keys then listed FK_Appointments_Doctors as CASCADE and FK_Appointments_Patients as NO_ACTION. The price is that deleting a patient who still has appointments is now refused:

DELETE FROM dbo.Patients WHERE Id = 1;

Msg 547, Level 16, State 1, Server MACHINE\LOCALDB#EAD42A01, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Appointments_Patients". The conflict occurred in database "fix8_lab", table "dbo.Appointments", column 'PatientId'.

So the application deletes the children first, on that path only:

DELETE FROM dbo.Appointments WHERE PatientId = 1;
DELETE FROM dbo.Patients WHERE Id = 1;

After those two statements no patients were left. Which key to stop cascading is a design choice: pick the path where an automatic delete would be surprising. Removing a patient's appointment history silently is usually worse than refusing, so the patient side is a good candidate, and explicit deletes in the application are easy to read and test.

How it was reproduced

One script run with sqlcmd -S "(localdb)\MSSQLLocalDB" -d fix8_lab -I -i e1785.sql on SQL Server 2022 LocalDB 16.0.1210.3 and sqlcmd 16.0.1000.6, in a throwaway database fix8_lab created for the test and dropped afterwards. Four tables (clinics, doctors, patients, appointments), plain T-SQL, no ORM, one fictional row in each table for the delete test.

Frequently asked

What does 'may cause cycles or multiple cascade paths' mean?

SQL Server error 1785 means the new foreign key, together with existing cascading keys, would let one delete reach the same table along two routes or loop back. SQL Server forbids that design.

How do I fix error 1785 in SQL Server?

Declare the new foreign key, or one of the existing ones on the same route, with ON DELETE NO ACTION. Then delete the child rows yourself before deleting the parent on that path.

Why does SQL Server reject cascade paths when the tables are empty?

The check runs on the constraint declarations when the key is created, not on the data. Two cascading routes to the same table are refused whatever rows exist.

Error 547 on the insert side has its own post, the INSERT statement conflicted with the FOREIGN KEY constraint. More decoded errors in the Fixes category.