SQL Server Multiple Cascade Path Solved with adding independent primary key

Question

I looked over several posts here on stack overflow about SQL Server error 1785: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths. And I found that it is a Microsoft product feature not to check for cycles and the common recommendation is to get around it with triggers.

So my question was:

How do I generate Foreign Key dependancies on multiple tables in SQL Server?

By mistake, I found that if I create a separate independent PRIMARY KEY, I can create the table with multiple cascade paths without an error (and it works functionally as expected). For reference I am running SQL Server 2019.

The following generates an error:

CREATE TABLE t1 (
    id bigint,
    CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (id)
);

CREATE TABLE t2 (
    id bigint,
    CONSTRAINT PK_t2 PRIMARY KEY CLUSTERED (id)
);

CREATE TABLE cross_t1_t2 (
    t1_id bigint,
    t2_id bigint,
    CONSTRAINT PK_cross_t1_t2 PRIMARY KEY CLUSTERED (t1_id,t2_id),
    CONSTRAINT FK_cross_t1_t2_t1 FOREIGN KEY (t1_id)     
        REFERENCES customers (id)     
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    CONSTRAINT FK_cross_t1_t2_t2 FOREIGN KEY (t2_id)     
        REFERENCES addresses (id)     
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Since I couldnt find this answer anywhere I am posting it here as an alternative to the trigger workaround. It would be nice to know why this is happening.

score 0 · Accepted Answer · answered Jun 05 '19 at 16:36

In order to work around the multiple cascade path error you can create a trigger to check and delete the record like mentioned here.

Or you can create a separate column as a primary key (can be auto increment IDENTITY)

The following does not generate an error:

CREATE TABLE t3 (
    id bigint,
    CONSTRAINT PK_t3 PRIMARY KEY CLUSTERED (id)
)

CREATE TABLE t4 (
    id bigint,   
    CONSTRAINT PK_t4 PRIMARY KEY CLUSTERED (id)
);
CREATE TABLE cross_t3_t4 (
    id      bigint,-- THE ONLY DIFFERENCE IS THE CREATION OF A SEPARATE PK                  
    t3_id   bigint,
    t4_id   bigint,
    --Constraints   
    CONSTRAINT PK_cross_t3_t4 PRIMARY KEY CLUSTERED (id),
    CONSTRAINT FK_cross_t3_t4_t3 FOREIGN KEY (t3_id)     
        REFERENCES t3 (id)     
        ON DELETE CASCADE    
        ON UPDATE CASCADE,
    CONSTRAINT FK_cross_t3_t4_t4 FOREIGN KEY (t4_id)     
        REFERENCES t4 (id)     
        ON DELETE CASCADE    
        ON UPDATE CASCADE
);

SQL Server Multiple Cascade Path Solved with adding independent primary key

1 Answers1