SQL Server: Constraint may cause cycles or multiple cascade paths

Patrik Harag, 2023-12-06

I was getting this exception:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Introducing FOREIGN KEY constraint 'ccc' on table 'BBB' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

I had the schema:

  ┌──────────┐
  │   AAA    │
  └─┬──────┬─┘
    │ n    │ n
    │      │
    │      │ 1
    │    ┌─┴────────┐
    │    │   BBB    │
    │    └─┬────────┘
    │      │ n
    │      │
    │ 1    │ 1
  ┌─┴──────┴─┐
  │   CCC    │
  └──────────┘

All tables have ON DELETE CASCADE on their foreign keys. The solution was to remove this clause from the foreign key (pointing to the CCC table) in the AAA table. Functionality is preserved, and the error disappears.

Note: The schema is not entirely clean (normalized) – life is complicated....

Note: This was not a problem with other databases.