Why is this a cyclical foreign key constraint?

Question

I came upon this code, marked "error," in an application I'm to update. Running it on a test database gives a cyclical reference error:

The referential relationship will result in a cyclical reference that is not allowed (Constraint name = descriptions_fk_2)

I named the constraints to see which one caused the problem.

CREATE TABLE items (
id INT NOT NULL UNIQUE IDENTITY,
name NCHAR(100) NOT NULL UNIQUE,
PRIMARY KEY (id)
);

CREATE TABLE sources (
id INT NOT NULL UNIQUE IDENTITY,
item_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (item_id)
REFERENCES items(id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE descriptions (
id INT NOT NULL UNIQUE IDENTITY,
item_id INT NOT NULL, 
source_id INT NOT NULL,
PRIMARY KEY (id),
CONSTRAINT descriptions_fk_1 FOREIGN KEY (item_id)
REFERENCES items(id) ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT descriptions_fk_2 FOREIGN KEY (source_id)
REFERENCES sources(id) ON UPDATE NO ACTION ON DELETE CASCADE
);

Why is this a cyclical reference? The descriptions table is linked to two separate tables, but none of them link back to descriptions.

Answer 1

It's not strictly cyclical - but there are multiple cascade paths. So you could cascade delete a row in items two ways:

1) description -> item
2) description -> source -> item

And, for that reason, it's disallowed.

I believe it's a performance concern, as PostGres will allow cycles like that and will just work it out, but deletes under those circumstances can be quite slow.

For some further reading about why it's disallowed, please see this answer.