I am not Fat
I am not Fat

Reputation: 447

how do I ensure that the combination of two columns is always unique?

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.

dbfiddle

how do I fix my exclude constrain?

Upvotes: 1

Views: 90

Answers (0)

Related Questions