Reputation: 447
I am currently in a situation where i need to write an exclusion constraint that ensures that
That every child keeps their parent lookup.
But I seem to run into issues here.
This is what I have tried:
CREATE TABLE IF NOT EXISTS parent
(
id integer PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS child
(
id integer PRIMARY KEY,
parent_lookup integer references parent(id)
);
ALTER TABLE child
DROP CONSTRAINT IF EXISTS parent_child_constraint,
ADD CONSTRAINT parent_child_constraint
EXCLUDE USING gist(id WITH =, parent_lookup WITH =)
-- This is valid operations and works as intended
insert into parent (id) values (1);
insert into parent (id) values (2);
insert into child (id , parent_lookup) values (1, 1);
insert into child (id , parent_lookup) values (2, 1);
so far so good..
-- this throws an error
insert into child (id , parent_lookup) values (2, 1); -- this shouldnot be invalid
A new child should be able to make a lookup to an existing parent - so throwing an error here is wrong.
-- this throws an error
insert into child (id , parent_lookup) values (1, 2); -- this should be invalid child cannot have a different parent
This throws an error as expected, since an existing child suddenly gets a new parent, which is not allowed.
how do I fix my exclude constrain?
Upvotes: 1
Views: 90