Why is this a circular foreign key constraint?

I came to this code marked "error" in the application that I have to update. Running it in a test database leads to a cyclic error:

The reference relation will result in a circular reference that is not allowed (Constraint name = descriptions_fk_2)

I called the restrictions 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 circular reference? A table descriptionsis associated with two separate tables, but not one of them refers to descriptions.

+5
source share
1 answer

This is not strictly cyclical, but there are several cascading paths. Thus, you can cascade a row deletion in elements in two ways:

1) description → item
2) → →

.

, , PostGres , .

, , . .

+6

All Articles