Postgresql: Conditionally unique constraint

postgresql constraints unique-constraint
EoghanM picture EoghanM · Apr 26, 2013 · Viewed 42k times · Source

I'd like to add a constraint which enforces uniqueness on a column only in a portion of a table.

ALTER TABLE stop ADD CONSTRAINT myc UNIQUE (col_a) WHERE (col_b is null);

The WHERE part above is wishful thinking.

Any way of doing this? Or should I go back to the relational drawing board?

Answer

Craig Ringer picture Craig Ringer · Apr 26, 2013

PostgreSQL doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index. PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, you just won't see the constraint listed in information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

See partial indexes.

Related questions

How to get the name of a unique constraint in postgresql?

I need to drop a unique constraint from a postgresql table, but I didn't give it a name in the schema. Does anybody know, how to get the name of such a constraint, or how to drop it?

Read More
Why can I not set this unique constraint in PostgreSQL?

I keep getting: SQL error: ERROR: could not create unique index "service_import_checksum_key" DETAIL: Key (checksum)=() is duplicated. In statement: ALTER TABLE "public"."service_import" ADD CONSTRAINT "service_import_checksum_key" UNIQUE ("checksum") But this constraint ISN'T a …

Read More
How to add "on delete cascade" constraints?

In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter? # \d scores Table "public.scores" Column | Type | Modifiers ---------+-----------------------+----------- id | character varying(32) | gid | integer | money | …

Read More