UNIQUE constraint on a specific pairing

Question

I am trying to work out a special UNIQUE constraint: there are many columns in a Users table but only these two are relevant for the question:

id bigint,
active boolean,
...

I want to allow multiple rows to have the same id - but only one of them may have active = true.

E.g. There can be many rows with id = 1 and active = false, but only one can have id = 1 and active = true.

Vérace · Accepted Answer · 2020-06-14T02:01:26.460

For this, you can make use of a very useful PostgreSQL feature - that of the partial index.

You have your table:

CREATE TABLE foo
(
  id BIGINT NOT NULL,
  --
  -- other fields
  --
  active BOOLEAN NOT NULL
);

Then, you do:

CREATE UNIQUE INDEX ON foo (id) 
WHERE active;  -- if you prefer more clarity, could have "WHERE active = TRUE;"

So, we try it out (see the fiddle here):

--
-- `active` - all false for a given id - works
--
INSERT INTO foo 
VALUES (1, FALSE), (1, FALSE), (1, FALSE);

check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f

Another possibility - the specific one pertaining to the question:

--
-- one and only one `active` = true for a given id - works
--
INSERT INTO foo 
VALUES (2, FALSE), (2, FALSE), (2, TRUE);

Check:

SELECT * FROM foo;

Result:

id  active
1   f
1   f
1   f
2   f
2   f
2   t

Final test:

--
--  attempt to INSERT two true `active` values for a given id - fails!
--
INSERT INTO foo
VALUES (3, FALSE), (3, FALSE), (3, TRUE), (3, TRUE);

Result:

ERROR:  duplicate key value violates unique constraint "foo_id_idx"
DETAIL:  Key (id)=(3) already exists.

The foo table is unchanged, which is what we want!

1 Answers1