Share via

Table constraint design to avoid a cycle

ritmo2k 911 Reputation points
2026-04-30T17:15:13.18+00:00

In the minimal reproducible example below, table C is invalid as it results in a cycle. I understand I can simplify C by removing the constraints, however the number of triggers required after goes up significantly if I want to preserve the cascading behavior from anywhere in the hierarchy (the actual use case more tables).

Based on the documentation, there are no foreign key constraints other than NO ACTION allowed on a table with an INSTEAD OF DELETE which would result in an error.

I would prefer not to split table B, but the actual use case is a lot larger and complex and it looks like I'd have to make all the foreign keys plain columns without references, and implement referential integrity manually.

Is the truly the only way?

CREATE TABLE A
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    Name NVARCHAR(256)    NOT NULL
);

CREATE TABLE B
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    AId UNIQUEIDENTIFIER NOT NULL,
    Name NVARCHAR(256)    NOT NULL,
    CONSTRAINT FK_B_A FOREIGN KEY (AId) REFERENCES A (Id) ON DELETE CASCADE
);

CREATE TABLE C
(
    Id UNIQUEIDENTIFIER NOT NULL DEFAULT (newsequentialid()) PRIMARY KEY,
    PrimaryId UNIQUEIDENTIFIER NOT NULL,
    SecondaryId UNIQUEIDENTIFIER NOT NULL,
    CONSTRAINT FK_C_PrimaryId FOREIGN KEY (PrimaryId) REFERENCES B (Id),
    CONSTRAINT FK_C_SecondaryId FOREIGN KEY (SecondaryId) REFERENCES B (Id)
);
SQL Server | SQL Server Transact-SQL

Answer recommended by moderator

Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator
2026-05-13T10:47:09.43+00:00

Hi @ritmo2k ,

Thanks everyone for the detailed discussion and clarification here.

The behavior described is expected due to SQL Server’s multiple cascade path restriction. It’s good to hear you were able to validate a working approach using a combination of declarative foreign keys where possible together with triggers for the more complex delete paths.

Keeping the foreign key constraints for referential integrity and using triggers only where necessary is generally a solid compromise for these scenarios.

Appreciate the additional context and insights shared by the community members.

Thanks,
Akhil.

Was this answer helpful?

0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator
    2026-04-30T18:34:03.6433333+00:00

    Yes, you cannot have two cascading FKs to the same table. One of the columns PrimaryId and SecondaryId can have cascading foreign key to B, but not both. As for why, well, Microsoft took a very conservative approach when they implemented cascading FKs. And maybe it was overly conservative.

    I am not sure what you have in mind when you talk about INSTEAD OF triggers, but I assume that you have some workaround in mind. While I guess that is doable, a little warning: the person that comes after you, should also understand what is going on the system. A better workaround, if more boring, is simply to explicitly delete child rows on the none-cascading key.

    In the system I work with now, we tend to more or less default to cascading foreign keys. But in another system I worked with for many years, we had a few of these and stuck to NO ACTION, calling for explicit deletes. Largely, this was out of habit. That system started its life before cascading FKs were introduced in SQL 2000.

    Was this answer helpful?

  2. AI answer

    2026-04-30T17:15:34.17+00:00

    SQL Server does not allow multiple cascade paths to the same table in a referential action tree. Error 1785 is raised when a FOREIGN KEY definition would create cycles or multiple cascade paths. In this case, having two FKs from C to B, both potentially cascading from A → B → C, is exactly such a scenario.

    To keep automated cleanup of C when dependencies above it are removed, while avoiding cycles/multiple cascade paths, the supported options are:

    1. Use NO ACTION on one or both FKs and implement cascading via triggers
      • Define the FKs on C without cascading actions (default NO ACTION): 
             CONSTRAINT FK_C_PrimaryId   FOREIGN KEY (PrimaryId)   REFERENCES B (Id) ON DELETE NO ACTION,
     CONSTRAINT FK_C_SecondaryId FOREIGN KEY (SecondaryId) REFERENCES B (Id) ON DELETE NO ACTION;
      • Then use DML triggers (for example, AFTER DELETE on B) to delete rows in C that reference the deleted B rows. Triggers are explicitly suggested in the documentation as a more flexible alternative when declarative referential integrity (DRI) is too restrictive.
      • This avoids multiple cascade paths while preserving referential integrity and automatic cleanup.
    2. Redesign the FK topology to ensure a single cascade path
      • Error 1785 guidance is to “create a foreign key that will create a single path to a table in a list of cascading referential actions.”
      • In practice, this usually means:
        • Removing cascading actions from some FKs and handling those cases manually or via triggers, or
        • Restructuring tables (for example, splitting B or C, or introducing an intermediate table) so that only one cascade path from A/B reaches any given table.
    3. Use constraints for basic integrity and triggers for complex rules
      • Constraints (PRIMARY KEY, FOREIGN KEY, CHECK) are the first line of defense for data integrity.
      • When the required behavior (like multi-branch cascading from several ancestors) cannot be expressed with DRI because of multiple cascade path restrictions, triggers are the recommended mechanism to enforce more complex integrity rules and bulk deletions.

    Given SQL Server’s restriction on multiple cascade paths, there is no purely declarative way to keep both FKs from C to B with full cascading from A/B and avoid the cycle/multiple-path issue. The practical pattern is to:

    • Keep the FKs for referential integrity (with NO ACTION), and
    • Implement the cascading delete behavior with triggers on B (and/or A) that delete from C.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.