Sql-server – Why do these foreign key constraints cause problems? (Azure SQL Server 12)

azureforeign keysql serversql server 2014

Here is a simplified version of the database I am attempting to create:

create table Worker (
    ID int,

    constraint PKW
    primary key (ID)
)

create table Business (
    ID int,
    W int,

    constraint PKB
    primary key (ID),

    constraint FKBW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

create table Note (
    ID int,
    B int,
    W int,

    constraint PKN
    primary key (ID),

    constraint FKNB
    foreign key (B) references Business (ID)
    on delete cascade on update cascade,

    constraint FKNW
    foreign key (W) references Worker (ID)
    on delete set null on update cascade
)

Essentially each business has a worker assigned to it, and any worker has the ability to create a note regarding any business. I have 3 foreign key constraints to reflect these relationships, but this creates the error:

Introducing FOREIGN KEY constraint 'FKNW' on table 'Note' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

There are no relationship cycles in this setup. I can see how having FKBW on delete cascade would create 2 delete paths to Note, but not with my current setup. Even if I remove on update cascade from both FKBW and FBNW, the error persists. I don't see how set null creates any problems.

Any clarity would be appreciated!

Best Answer

The problem seem to be the ON UPDATE CASCADEs, the ON DELETEs are fine. I don't get an error if I remove the ON UPDATEs.

According to the accepted answer in "Foreign key constraint may cause cycles or multiple cascade paths?" on Stack Overflow, SQL Server does no "deep" inspection of the cascade paths but just a shallow one. If there is any cascading constraint to a table A in a table B, that already induces an edge in the cascade graph between A and B, regardless of the targeted columns.

So here we have a path directly from Worker to Note and a path from Worker via Business to Note. Note appears twice in the graph.

Though here, this raises no conflict on a specific column, this is already enough as the Microsoft support article "Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths:" states:

You receive this error message because in SQL Server, 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. For example, the tree of cascading referential actions must only have one path to a particular table on the cascading referential actions tree.

Triggers are suggested to work around this. However, I see a major problem with triggers, as I don't know any way to map rows from the inserted pseudo table with them of the deleted pseudo table other than via the primary key. But if that has changed in the update, there is no possible match and no way to detect the pair of changed values.

So I guess there is no way other than not updating primary keys and use an ON UPDATE NO ACTION. And usually one doesn't really need to update primary keys anyway, even though it would be a nice to have as an option.

Related Question