SQL Server Multiple Cascade Paths (Error 1785): Which Foreign Key Loses ON DELETE CASCADE, and What Replaces It

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: SQL Server refuses a foreign key when its CASCADE, SET NULL or SET DEFAULT would let one delete or update reach the same table by two paths, and it decides that from the declared actions when the key is created, not from the rows. One key has to become NO ACTION, which makes some deletes fail with error 547 until a stored procedure or an INSTEAD OF DELETE trigger on the parent removes the child rows first. An AFTER DELETE trigger cannot do it, and SET NULL on the second key is refused like CASCADE.

Error 1785 means two of your foreign keys would both act on the same table when one parent row is deleted or updated, and SQL Server will not create the second one, even on empty tables. The fix has two parts. One of the keys has to become NO ACTION, and then something else has to remove the rows that key used to remove, because a NO ACTION key turns some deletes into error 547. Keep the cascade on the key that says who owns the row, and replace the other one with a stored procedure or an INSTEAD OF DELETE trigger on the parent table.

Everything below ran on SQL Server 2022 (16.0.4265.3, CU26) in a throwaway Docker container, and again on SQL Server 2025 (17.0.5035.5) with the same results. The schema is three tables: users, posts with a user_id, and comments with a post_id and a user_id.

Why does SQL Server say a foreign key may cause cycles or multiple cascade paths?

Because deleting one row would reach the same table by two routes, and SQL Server allows only one route to each table per delete or update.

A comment belongs to a post and was written by a user, and a post was written by a user. Give all three keys ON DELETE CASCADE and a deleted user reaches comments twice: directly, and through posts.

CREATE TABLE users (
  id INT IDENTITY PRIMARY KEY,
  name NVARCHAR(50) NOT NULL
);

CREATE TABLE posts (
  id INT IDENTITY PRIMARY KEY,
  user_id INT NOT NULL,
  title NVARCHAR(200) NOT NULL,
  CONSTRAINT fk_posts_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE
);

CREATE TABLE comments (
  id INT IDENTITY PRIMARY KEY,
  post_id INT NOT NULL,
  user_id INT NOT NULL,
  body NVARCHAR(1000) NOT NULL,
  CONSTRAINT fk_comments_post FOREIGN KEY (post_id)
    REFERENCES posts (id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id)
    REFERENCES users (id) ON DELETE CASCADE
);
-- Msg 1785, Level 16, State 1
-- Introducing FOREIGN KEY constraint 'fk_comments_user' on table 'comments'
-- 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: Could not create constraint or index. See previous errors.

The comments table is not created at all. Microsoft's page for error 1785 gives the rule: "a table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement." The check reads the declared actions only. There were no rows in any table when it failed.

The key the message names is the one that arrived second, not the one that is wrong. With both keys in one CREATE TABLE it named fk_comments_user. When I created comments with only the user key and added the post key afterwards with ALTER TABLE, the same error named fk_comments_post.

PostgreSQL 18.3 and MySQL 8.4.11 created the same three keys without complaint, and deleting one user there removed that user's post and every comment on either path. So this is a rule of SQL Server, not of SQL, and it tends to arrive from an ORM: Entity Framework Core configures required relationships to cascade by default, and its documentation on database cascade limitations shows this exact exception on a blog, post and person model.

The diamond is one of three shapes SQL Server refused, along with two keys from one parent and a self-reference:

Shape	Keys	Result
Direct and indirect path into one table	users to posts to comments, plus users to comments, all CASCADE	Msg 1785
The same, with SET NULL on comments.user_id	CASCADE, CASCADE, SET NULL	Msg 1785
The same, with SET NULL on comments.post_id	CASCADE, SET NULL, CASCADE	Msg 1785
Two keys from one parent	messages.sender_id and messages.recipient_id to users, both CASCADE	Msg 1785
Two keys from one parent	one CASCADE, one SET NULL	Msg 1785
Two keys from one parent	one CASCADE, one NO ACTION	Created
Self-reference	categories.parent_id to categories, CASCADE or SET NULL	Msg 1785
Two ON UPDATE CASCADE keys from one parent	transfers.from_id and transfers.to_id to users	Msg 1785

SET NULL does not get around it, and ON UPDATE is checked the same way as ON DELETE.

Which foreign key should be NO ACTION?

The key that records a reference rather than ownership: keep ON DELETE CASCADE from posts to comments, because a comment cannot exist without its post, and make comments.user_id the NO ACTION key.

Either choice makes SQL Server accept the schema, and either choice makes some deletes fail. (A third, NO ACTION on posts.user_id, is accepted too, but then deleting any user who has a post fails with Msg 547 on fk_posts_user, so it is not a candidate.) I loaded three users, one post each, and four comments: Ana on her own post, Ana on Ben's post, Ben on Ana's post, and Cy on his own post. Then I tried both versions.

Delete	NO ACTION on comments.user_id	NO ACTION on comments.post_id
A post that has comments	Works, the comments go with it	Fails, Msg 547 on fk_comments_post
Cy, who only commented on his own post	Works	Works
Ana, who commented on Ben's post and has Ben's comment on hers	Fails, Msg 547 on fk_comments_user	Fails, Msg 547 on fk_comments_post

The failure reads:

Msg 547, Level 16, State 1
The DELETE statement conflicted with the REFERENCE constraint "fk_comments_user".
The conflict occurred in database "blog", table "dbo.comments", column 'user_id'.
The statement has been terminated.

Cy's delete works in both versions because SQL Server applies every cascade before it checks a NO ACTION key, so by the time it looks, his one comment is already gone with his post. Ana's fails because a row survives the cascades and still points at something being deleted.

That makes the first column the better schema. With NO ACTION on comments.post_id, deleting any post that has a comment fails, which breaks the most ordinary delete in the application. With NO ACTION on comments.user_id, posts delete normally and only one operation needs help: deleting a user who has commented on someone else's post. The schema is accepted, and the delete that used to be one statement is now your job.

What replaces the cascade you removed?

A stored procedure, or an INSTEAD OF DELETE trigger on the parent table, that removes the child rows before the parent row; an AFTER DELETE trigger cannot do it.

Replacement	Result for deleting Ana
AFTER DELETE trigger on users that deletes her comments	Still Msg 547. The key is checked before the trigger fires, so it never runs
INSTEAD OF DELETE trigger on users	Works: Ana, her post and every comment on either path are gone
Stored procedure that deletes her comments, then her, in one transaction	Works, same rows removed
ON DELETE SET NULL on comments.user_id	Not available, Msg 1785

The trigger that worked:

CREATE TRIGGER trg_users_delete ON users INSTEAD OF DELETE AS
BEGIN
  SET NOCOUNT ON;
  DELETE FROM comments WHERE user_id IN (SELECT id FROM deleted);
  DELETE FROM users WHERE id IN (SELECT id FROM deleted);
END;

It reads the deleted table as a set, so DELETE FROM users WHERE id IN (2, 3) handled both users in one statement. The second DELETE inside it does not fire the trigger again; it runs the real delete, and fk_posts_user and fk_comments_post cascade as declared.

If a comment should outlive its author, the trigger is also where the null goes, since SQL Server refuses SET NULL on that key. Make comments.user_id nullable and swap the first statement for UPDATE comments SET user_id = NULL WHERE user_id IN (SELECT id FROM deleted). Deleting Ana then left her comment on Ben's post in place with no author, and removed her post and the comments on it.

The trigger has three limits, all measured:

• It only fits a table that has no cascading foreign key of its own. CREATE TRIGGER ... ON posts INSTEAD OF DELETE failed with Msg 2113, "Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trg_posts_delete' on table 'posts'. This is because the table has a FOREIGN KEY with cascading DELETE or UPDATE.", because posts.user_id cascades from users.
• It closes a door on the table that has it. With the trigger on users, adding users.org_id with ON DELETE CASCADE to an orgs table failed with Msg 1787. The same key with ON DELETE SET NULL was accepted.
• It breaks a DELETE that has an OUTPUT clause. DELETE FROM users OUTPUT 1 WHERE id = 3 failed with Msg 334, "The target table 'users' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause." EF Core 7 and later save changes with OUTPUT by default, so the entity has to be configured with HasTrigger before EF Core can write to that table.

A stored procedure has none of these limits, and it is the same two statements in a transaction:

CREATE PROCEDURE delete_user @user_id INT AS
BEGIN
  SET NOCOUNT ON; SET XACT_ABORT ON;
  BEGIN TRANSACTION;
  DELETE FROM comments WHERE user_id = @user_id;
  DELETE FROM users WHERE id = @user_id;
  COMMIT;
END;

Its cost is that it only protects callers who use it. A plain DELETE FROM users from another service, a script or an ORM still fails with Msg 547, which at least fails loudly rather than leaving orphans. Doing the same two statements from application code is equivalent, and it is what EF Core's ClientCascade option does for rows it has loaded.

How do you delete a tree when a self-reference cannot cascade?

Leave the self-referencing key at NO ACTION and delete the whole subtree in one statement with a recursive common table expression.

SQL Server refused categories.parent_id with ON DELETE CASCADE and with ON DELETE SET NULL. With the key at NO ACTION, deleting a category that has children fails with Msg 547, "The DELETE statement conflicted with the SAME TABLE REFERENCE constraint". Collecting the subtree first works, because the key is checked against the rows the whole statement leaves behind, and parent and children go together:

WITH tree AS (
  SELECT id FROM categories WHERE id = 1
  UNION ALL
  SELECT c.id FROM categories c JOIN tree t ON c.parent_id = t.id
)
DELETE FROM categories WHERE id IN (SELECT id FROM tree)
OPTION (MAXRECURSION 0);

On four rows, a chain of 1, 2 and 3 plus an unrelated row 4, that statement removed the chain and left row 4. The OPTION (MAXRECURSION 0) matters on a deep tree: SQL Server stops a recursive query after 100 levels by default, and without the option a chain of 150 categories failed with Msg 530, "The maximum recursion 100 has been exhausted before statement completion", and deleted nothing.

How Schemity flags a second cascade path before SQL Server does

Schemity's lint reports the foreign key that adds the second path while it is still a line on the diagram, and names the key it collides with.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git. On a SQL Server diagram, whether it is connected to a database or a design with no connection yet, the ON DELETE and ON UPDATE actions are set in the relationship dialog, and nothing stops you from choosing CASCADE on both of a table's keys. On an unconnected design there is no server to object, so the first place the problem can show is schema lint.

Changing that last key to CASCADE is one dropdown in the dialog, and Schemity accepts it:

The rule is sqlserver-multiple-cascade-paths, in the "Fails at runtime" group, and it runs only on SQL Server diagrams. For the three tables above with all keys at CASCADE, the finding sits on comments.user_id:

SQL Server refuses this foreign key: its ON DELETE action makes a second path between the same tables (Msg 1785).

ON DELETE in users reaches comments through fk_comments_post and through this key. One of the two has to be NO ACTION.

On a connected diagram the finding also follows the change into Preview changes, next to the planned migration it would run. The preview draws only users and comments, the two tables the change touches, but lint reads the whole schema, so the path through posts is still counted:

Statement 4 in that migration is the one SQL Server would answer with Msg 1785. Schemity runs a SQL Server migration in one transaction and rolls it back on the first error, so the DROP CONSTRAINT before it would be undone and the old NO ACTION key would stay in place.

It covers the same shapes as the table earlier: a direct and an indirect path, two acting keys from one parent, and a self-reference, where the detail says that the rows below have to be handled by the application or a trigger. It checks deletes and updates separately, as SQL Server does, and it counts SET NULL and SET DEFAULT as acting keys, so switching the second key to SET NULL keeps the finding. It also follows one subtlety I confirmed on the server: an ON DELETE SET NULL in the middle of a chain ends that path, because the row it changes is not deleted (an ON UPDATE chain has no such exit). users to members with CASCADE, members to tasks with SET NULL, then tasks to notes with CASCADE and users to notes with SET NULL was created without error, and lint stays silent on it.

What lint cannot tell you is which key to change, because that depends on which table owns the row. That decision is the one in the second section, and it is easier to make on an ERD, where both paths from users to comments are visible as lines, than in a migration file where the two keys are forty lines apart. When you design the next one, set the reference key to NO ACTION in the dialog, and the finding clears before any SQL is written.

Two related posts pick up from here: ON DELETE SET NULL vs CASCADE in PostgreSQL covers choosing between the two actions where both are allowed, and circular foreign keys covers the insert side of a cycle, which SQL Server permits as long as the actions do not go round it.