0

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.

5

Use a composite foreign key:

(tree_id, parent_id) REFERENCES (tree_id, id)

You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.