Search code examples
postgresqlconstraintsunique

Postgres Unique Constraint on two columns: Integer and Boolean

I would like to find a way to store multiple addresses for one subject in my database, with only one default address per subject.

To cut a long story short, lets say we have a table:

CREATE TABLE test
(
  id integer NOT NULL,
  active boolean NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)

For each id in the table, there must be at most 1 true active value.

How could I achieve this?

Thanks.

Solution

  • Partial index is what you want:

    create unique index some_index on test (id) where active;

    As @ThiefMaster states primary index should be removed.