Why does SQL Server prevent me from creating these foreign keys?

Question

My (abridged) schema has (or rather, would need) the following tables and relationships:

Clients (ID INT PRIMARY KEY)
Files (
    ID INT PRIMARY KEY,
    Client INT REFERENCES Clients(ID) ON DELETE CASCADE)
Interfaces (
    ID INT PRIMARY KEY,
    Client INT REFERENCES Clients(ID) ON DELETE CASCADE)
Files_Interfaces (
    "File" INT REFERENCES Files(ID) ON DELETE CASCADE,
    Interface INT REFERENCES Interfaces(ID) ON DELETE CASCADE)

So as you can see from this schema, a Client can have multiple files and multiple interfaces. There is a many-to-many relationship between interfaces and files (hence the junction table Files_Interfaces), and the only hierarchical relationships are to Client (there is a one-to-many relationship from Clients to Files and Interfaces).

I'd need these constraints between Files_Interfaces and Files and Interfaces so that, if I delete a file or an interface, all the related associations are destroyed.

The problem is, SQL Server 2005 won't let me have this schema. When I try to create Files_Interfaces with such constraints, I get the following error:

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

What gives? Is it because if I delete a client, then both files and interfaces are deleted and then Files_Interfaces records have two good reasons to disappear? If so, why is it a bad thing?

Can I somehow prevent this error?

in theory, your schema is viable and you could actually create it in, say, `PostgreSQL`. It's `Microsoft`'s implementation that does not allow you to do that. — Quassnoi, Mar 31 '11 at 14:04

score 5 · Accepted Answer · answered Mar 31 '11 at 13:59

While there is nothing wrong with multiple cascade paths (and even cycles) in theory, the implementation of them may be hard.

As it is now, SQL Server requires that the cascade graph should be a tree rather than a directed acyclic (or even worse, cyclic) graph:

The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE. Also, the tree of cascading referential actions must not have more than one path to any specified table. Any branch of the tree is ended when it encounters a table for which NO ACTION has been specified or is the default.

Due to implementation limitations, SQL Server does not optimize cascade deletes as a single set-based operation, rather, it splits the tree into a series of individual joins. In case of A -> B -> C, DELETE A is not a DELETE A JOIN B JOIN C but rather a DELETE A JOIN B OUTPUT B_temp followed by DELETE B_temp JOIN C.

In case of multiple cascade paths, it would lead to multiple B_temp.

Why does SQL Server prevent me from creating these foreign keys?

1 Answers1