CODEWITHSUNDEEP
postgresql
Bharath
Bharath

Reputation: 1807

Postgres multi column value based constraint?

key            | value   |  other         
----------------------------------- 
key1           | red     |  r1          
key1           | blue    |  b1  
key1           | blue    |  b2         
key2           | green   |  g
key2           | yellow  |  y

In postgres, how do i define a constraint such that a value is only associated to one of the keys. Ex: red can only have key1, green can only have key2? 

key            | value   |  other         
----------------------------------- 
key1           | red     |  r1          
key1           | blue    |  b1  
key1           | blue    |  b2         
key2           | blue    |  r4
key2           | yellow  |  y

This is not allowed because value blue is associated to key1 & key2.

Upvotes: 0

Views: 62

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

The proper solution is a redesign so that you normalize the tables:

CREATE TABLE part1 (
   value text PRIMARY KEY,
   key text NOT NULL
);

CREATE TABLE part2 (
   value REFERENCES part1(value) NOT NULL,
   other text NOT NULL
);

Then the constraint is guaranteed by the table structure, and you don't have to store redundant data multiple times.

Upvotes: 1

Related Questions