Add constraint to make column unique per group of rows

Question

There is a column status in a Postgres table which can take only two values: Active and Inactive.

One of the columns is named userid. The table can have multiple rows with the same userid but at most one of them can have status = 'Active'. I need only one or no status as Active per userid. How can I create a constraint with this condition? I couldn't find any help from the Postgres documentation.

Answer 1

status should really be boolean. Cheaper, cleaner.

Either way, you can impose your rule with a partial unique index.

To allow zero or one row with status = 'Active' in the whole table:

CREATE UNIQUE INDEX tbl_active_uni ON tbl (status)
WHERE status = 'Active';

To allow zero or one row with status = 'Active' per userid, make userid the indexed column:

CREATE UNIQUE INDEX tbl_userid_active_uni ON tbl (userid)
WHERE status = 'Active';

Note that userid IS NULL would not trigger unique violations, because two NULL values are never considered equal. userid must be set NOT NULL in this case.

• How to add a conditional unique index on PostgreSQL
• Create unique constraint with null columns

Why use an index and not a constraint?

Addressing your question in the comment: This is an index, not a CONSTRAINT.

The index for the first case is tiny, holding one or no row.
The index for the second case holds one row per existing userid, but it's the cheapest and fastest way, in addition to being clean and safe. You would need an index to check on other rows in any case to make this fast.

You cannot have a CHECK constraint check on other rows - at least not in a clean, reliable fashion. There are ways I would certainly not recommend for this case:

• Trigger vs. check constraint
• How to avoid a cyclic dependency (circular reference) between 3 tables?
• Disable all constraints and table checks while restoring a dump

If you use a UNIQUE constraint on (userid, status) (which is also implemented with a unique index internally!), you cannot make it partial, and all combinations are enforced to be unique. You could still use this if you work with status IS NULL for all cases except the 'Active' case. But that would actually impose a much bigger index including all rows.