CODEWITHSUNDEEP
postgresql
dirtboy3000
dirtboy3000

Reputation: 11

POSTGRES multiple to one constraint

I have two colums, foo and bar. foo can only exist with one bar, but can exists n times. If I try to insert foo with a different value of bar, it needs to fail.

 foo | bar
----------
  a  |  a
  a  |  a
  b  |  a
  b  |  a
  b  |  b  <---  this needs to fail.

How can I solve this?

And somewhat tangentially what is the proper language for this question?

Upvotes: 1

Views: 45

Answers (1)

Marth
Marth

Reputation: 24802

You can use an EXCLUDE constraint.
Using the extension btree_gist:

CREATE EXTENSION btree_gist;

You can define a table

CREATE TABLE fb (
  foo text,
  bar text,
  EXCLUDE USING gist (foo WITH =, bar WITH <>)
);

such that the following insertion is ok:

#= INSERT INTO fb VALUES ('a', 'a'), ('a', 'a'), ('b', 'b');
INSERT 0 3
Time: 1,392 ms
#= SELECT * FROM fb;
┌─────┬─────┐
│ foo │ bar │
├─────┼─────┤
│ a   │ a   │
│ a   │ a   │
│ b   │ b   │
└─────┴─────┘
(3 rows)

but inserting the ('b', 'a') tuple fails because 'b' already has a bar = 'b' row.

#= INSERT INTO fb VALUES ('b', 'a');
ERROR:  23P01: conflicting key value violates exclusion constraint "fb_foo_bar_excl"
DETAIL:  Key (foo, bar)=(b, a) conflicts with existing key (foo, bar)=(b, b).
SCHEMA NAME:  public
TABLE NAME:  fb
CONSTRAINT NAME:  fb_foo_bar_excl

Edit: Though this works I agree with Conrad Frix's comment, normalizing your table is probably a way better idea.

Upvotes: 3

Related Questions