Exclusion constraint on recursive foreign key (on the example of a Tree)
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)