SQL Server 1785: cycles or multiple cascade paths

What it means

ON DELETE CASCADE (and SET NULL, SET DEFAULT, and the same for ON UPDATE) makes SQL Server change child rows when a parent row changes. SQL Server insists that, from any table, there’s at most one chain of such actions to any other table, and no chain that leads back to where it started. The foreign key you’re adding would create a second chain or a loop, so SQL Server refuses to create it:

Msg 1785, Level 16, State 1, Server 7732422b7f56, Line 1
Introducing FOREIGN KEY constraint 'FK_tasks_users' on table 'tasks' 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 7732422b7f56, Line 1
Could not create constraint or index. See previous errors.

It’s a rule about the shape of the schema, checked when the constraint is created, not about your data: SQL Server refuses even when the two paths could never reach the same row. PostgreSQL accepts the same schema, so this often appears when a schema or an ORM model moves to SQL Server. If the statement was a CREATE TABLE, the table isn’t created either.

Common causes

A diamond. users → projects → tasks cascades, and tasks.assignee_id → users would cascade too: deleting a user reaches tasks two ways.
Two foreign keys from one table to the same parent, both cascading: messages.sender_id and messages.recipient_id both referencing users.
A table that references itself with a cascading action: categories.parent_id → categories.id ON DELETE CASCADE is a cycle.
ORM defaults. EF Core configures required relationships (a non-nullable foreign key) to cascade delete by default, so a model with two required paths fails when the migration runs.
SET NULL on the second path. It counts as a cascading action, so changing CASCADE to SET NULL doesn’t help; only NO ACTION does.

How to fix it

Keep one cascading path, and handle the other yourself

Pick the path that should cascade, and make the other foreign key NO ACTION (the default when you write no action):

ALTER TABLE dbo.tasks
  ADD CONSTRAINT FK_tasks_users FOREIGN KEY (assignee_id) REFERENCES dbo.users (id);

Then clear or delete those child rows before deleting the parent, in one transaction:

BEGIN TRANSACTION;
UPDATE dbo.tasks SET assignee_id = NULL WHERE assignee_id = @user_id;
DELETE FROM dbo.users WHERE id = @user_id;   -- projects, and their tasks, cascade
COMMIT;

Without the first statement, the delete fails with error 547 as long as a task is assigned to that user.

Or let an INSTEAD OF DELETE trigger do it

An INSTEAD OF DELETE trigger on the parent runs in place of the DELETE, so it can deal with the non-cascading children first and then delete the rows itself. Every delete of a user, from any code, then works:

CREATE TRIGGER dbo.users_delete ON dbo.users INSTEAD OF DELETE AS
BEGIN
  SET NOCOUNT ON;
  UPDATE t SET assignee_id = NULL
  FROM dbo.tasks AS t JOIN deleted AS d ON d.id = t.assignee_id;

  DELETE u FROM dbo.users AS u JOIN deleted AS d ON d.id = u.id;
END;

Triggers act invisibly, so name them clearly and keep them short. Write them for many rows at once (join to deleted), since one DELETE can remove many users.

Self-references: delete the tree yourself

A self-referencing foreign key can’t cascade at all. Keep it NO ACTION and delete a branch from the leaves up, for example with a recursive CTE that collects the ids, ordered by depth; or keep the rows and mark them deleted.

In EF Core, change the relationship’s delete behaviour

Configure one of the relationships with .OnDelete(DeleteBehavior.NoAction) (or ClientCascade, which cascades only for rows EF has loaded), or make its foreign key nullable so it’s optional and doesn’t cascade by default. Then add the migration again.

Reproduce it

On SQL Server 2022 (16.0.4295.3) with sqlcmd, in a scratch schema: users, projects with owner_id REFERENCES users ON DELETE CASCADE, and tasks with project_id REFERENCES projects ON DELETE CASCADE and a nullable assignee_id. Adding FK_tasks_users on assignee_id with ON DELETE CASCADE gave the 1785 and 1750 shown above, and ON DELETE SET NULL gave the same pair. A self-referencing categories table, and a messages table with two cascading keys to users:

Msg 1785, Level 16, State 1, Server 7732422b7f56, Line 1
Introducing FOREIGN KEY constraint 'FK_categories_parent' on table 'categories' 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 7732422b7f56, Line 1
Could not create constraint or index. See previous errors.
Msg 1785, Level 16, State 1, Server 7732422b7f56, Line 1
Introducing FOREIGN KEY constraint 'FK__messages__recipi__38D961D7' on table 'messages' 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 7732422b7f56, Line 1
Could not create constraint or index. See previous errors.

Neither table was created. FK_tasks_users with no action worked. With a task assigned to user 2, DELETE FROM users WHERE id = 2 then failed with 547 on FK_tasks_users. With the trigger above (inside transactions we rolled back), deleting user 2 set that task’s assignee_id to NULL, and deleting user 1 removed the user, their project and its tasks. On PostgreSQL 18.6 the diamond and the self-reference were both accepted with ON DELETE CASCADE. SQL Server 2019 and 2025 printed the same messages.

In Inlet

A table’s definition shows its CREATE TABLE with each foreign key and its ON DELETE action, and its triggers. When SQL Server refuses the constraint, Inlet shows its message with SQL Server error 1785, state 1, severity 16 and links to this page.

Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths