Postgres multi column value based constraint?

Question

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.

To rephrase your question, you're trying to enforce a one-to-one mapping between unique keys and values, correct? — Andrew Fan
– Andrew Fan, Commented Sep 5, 2018 at 21:16
kind of like an injective function. distinct elements on the left(key) cannot map to the same elements on the right(value). — Bharath
– Bharath, Commented Sep 5, 2018 at 21:24
@Bharath To clarify, your table can contain multiple rows with the same key, correct? And if the same key is repeated, they all must have the same value field? — Sam Choukri
– Sam Choukri, Commented Sep 6, 2018 at 4:34

Laurenz Albe · Accepted Answer · 2018-09-06 06:35:27Z

1

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.

answered Sep 6, 2018 at 6:35

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bharath Over a year ago

Thanks laurenz. This is the correct design to enforce the constraint. I wasn't sure thinking it will create too many tables.

Collectives™ on Stack Overflow

Postgres multi column value based constraint?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related