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

> 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`.

Source: https://schemity.com/blog/sql-server-multiple-cascade-paths-error-1785/

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`.

```sql
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](https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-1785-database-engine-error) 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](https://learn.microsoft.com/en-us/ef/core/saving/cascade-delete#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:

```sql
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:

```sql
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:

```sql
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](https://schemity.com/doc/connect-sql-server/) or a design with no connection yet, the `ON DELETE` and `ON UPDATE` actions are set in the [relationship dialog](https://schemity.com/doc/relationships/), 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](https://schemity.com/doc/schema-lint/).

![The users, posts and comments tables read from a SQL Server 2022 database in Schemity: users to posts and posts to comments drawn with a bold crow's foot because they cascade, and a second line from users straight to comments.user_id, which is NO ACTION](https://schemity.com/images/blog/sql-server-multiple-cascade-paths-error-1785/canvas-users-posts-comments.webp)

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

![Schemity's Update relation dialog for users.id to comments.user_id, constraint fk_comments_user, Zero or Many, with On delete set to CASCADE and On update left at NO ACTION](https://schemity.com/images/blog/sql-server-multiple-cascade-paths-error-1785/relation-comments-user-cascade.webp)

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.

![Schemity's Lint drawer after comments.user_id is set to ON DELETE CASCADE on SQL Server 2022: under Fails at runtime, the finding on fk_comments_user says SQL Server refuses this foreign key (Msg 1785), and the canvas marks the comments.user_id row in red with the relation from users drawn dotted](https://schemity.com/images/blog/sql-server-multiple-cascade-paths-error-1785/lint-multiple-cascade-paths.webp)

On a connected diagram the finding also follows the change into Preview changes, next to [the planned migration](https://schemity.com/doc/migration-sql-diff/) 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:

![Schemity's change preview for the edited key on SQL Server 2022: the planned migration drops fk_comments_user and adds it again with ON DELETE CASCADE, Planned changes lists the foreign key on comments (user_id) as changed, and Lint: 4 includes comments.user_id, SQL Server refuses this foreign key (Msg 1785)](https://schemity.com/images/blog/sql-server-multiple-cascade-paths-error-1785/preview-fk-cascade-lint.webp)

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](https://schemity.com/blog/postgres-on-delete-set-null-vs-cascade/) covers choosing between the two actions where both are allowed, and [circular foreign keys](https://schemity.com/blog/circular-foreign-keys-first-row-cannot-be-inserted/) covers the insert side of a cycle, which SQL Server permits as long as the actions do not go round it.

## Frequently asked questions

### Does SQL Server error 1785 depend on the data in the tables?

No. SQL Server raises error 1785 when the foreign key is created, from the `ON DELETE` and `ON UPDATE` actions the keys declare, so it fails on empty tables as well. No row has to exist that would be reached twice. That is why it shows up in the migration that adds the key, and never at delete time.

### Can I use `ON DELETE SET NULL` instead of `ON DELETE CASCADE` to avoid multiple cascade paths?

Not on the key that makes the second path. On SQL Server 2022 and 2025, a key with `ON DELETE SET NULL` was refused with the same error 1785 as one with `ON DELETE CASCADE`, in a diamond of three tables and for two keys from one parent. Only `NO ACTION` is accepted there, so the null has to be written by a trigger or a stored procedure.

### Do PostgreSQL and MySQL have the multiple cascade paths restriction?

No. PostgreSQL 18.3 and MySQL 8.4.11 both created the same three `ON DELETE CASCADE` keys that SQL Server refused, and deleting one user removed that user's posts and every comment on either path. A schema designed on one of them can fail with error 1785 when it is moved to SQL Server.