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.

This is not a good design. Since a source is already referenced to a item, you shouldn't add `item_id` on the table `descriptions`, it's enough with the `source_id` — Lamak, Jul 09 '12 at 15:41
@Lamak I noticed that too. The code made me curious nevertheless. — Ricardo Altamirano, Jul 09 '12 at 15:41
It's most likely an issue of multiple cascade paths. Refer to this SO post: http://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths — ssis_ssiSucks, Jul 09 '12 at 15:42
@JimMcKeon Thanks for the link. That explains it quite nicely. — Ricardo Altamirano, Jul 09 '12 at 15:47

score 7 · Accepted Answer · edited May 23 '17 at 10:34

7

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.

edited May 23 '17 at 10:34

Community

1
1

answered Jul 09 '12 at 15:38

Matt Whitfield

6,436
3
29
44

1

Maybe add a link to [this answer](http://stackoverflow.com/a/852047/869912) into your post, since it explains everything quite nicely? – Ricardo Altamirano Jul 09 '12 at 15:47
1

I have added the link, but, I have to say, I don't think it's as big as problem as it's made out to be. It's fairly trivial to write code that walks a cycle tree, and adds to a 'rows to delete' list. The problem should only arise when different cascade paths have different actions, as this would then cause a conflict. That's why I said I believe it to be a performance issue, because walking that tree on a very large table could get pretty expensive pretty quickly. – Matt Whitfield Jul 09 '12 at 15:55

Why is this a cyclical foreign key constraint?

1 Answers1