This migration creates a trigger function prevent_cycle
to be used to prevent foreign key cycles in self-referential tables.
The first argument of the trigger function is the table's name; the second argument is the table's self-referential foreign key column.
Currently, it is assumed that the table has the column id
, though this could easily be change.
CREATE TABLE hierarchy (
id BIGSERIAL PRIMARY KEY,
parent_id BIGINT REFERENCES hierarchy(id)
);
CREATE TRIGGER prevent_hierarchy_cycle
AFTER INSERT OR UPDATE OF parent_id ON hierarchy
FOR EACH ROW
EXECUTE PROCEDURE prevent_cycle('hierarchy', 'parent_id');
INSERT INTO hierarchy (id, parent_id) VALUES (1, NULL);
INSERT INTO hierarchy (id, parent_id) VALUES (2, 1);
UPDATE hierarchy SET parent_id = 2 WHERE id = 1; -- Exception raised!
Hi @SkylerLipthay! Your trigger works like charm. Have you decided for a license by any chance?