I have the table with recursive foreign key. It's just hierarchical tree structure:
CREATE TABLE tree (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
value INTEGER NOT NULL,
tree_id SMALLINT NOT NULL CHECK (tree_id > CAST(0 AS SMALLINT)),
parent_id INTEGER REFERENCES tree ON DELETE RESTRICT ON UPDATE RESTRICT,
EXCLUDE (tree_id WITH =) WHERE (parent_id IS NULL) -- allow only one root within tree
);
How to reject inserting records with non-null parent_id
and different tree_id
? I.e. foreign key parent_id
can have reference only to primary key id
within the same tree_id
.
Best Answer
Use a composite foreign key:
You will need to first change the PK or add a
UNIQUE
constraint on(tree_id, id)