Restrict two specific column values from existing at the same time

Posted on

Question :

I have a PostgreSQL example table where at most one row that is not of type ‘c’ should be allowed.

I would appreciate any help creating a constraint that will enforce this.

  example_id    serial PRIMARY KEY,
  example_state CHAR(1) NOT NULL

CHECK (example_state = 'a' OR example_state = 'b' OR example_state = 'c');

Answer :

One row per rare type

If you want to allow 0-n rows with type ‘c’ and only 0-1 rows for type ‘a’ and ‘b’ (each), you can combine a simple CHECK constraint with a partial unique index:

CREATE TABLE example (
  example_id    serial PRIMARY KEY,
  example_state "char" NOT NULL CHECK (example_state IN ('a', 'b', 'c'))

CREATE UNIQUE INDEX example_unique_exception_idx ON example (example_state)
WHERE example_state <> 'c'; -- column is not null;

Related answer:

An exlusion constraint would be a related concept, but it won’t work with multiple entries for 'c'. You could use NULL instead of ‘c’ to make that work.

Also note the special data type "char" (in double-quotes!), which seems to be perfect for your simple purpose (unless it’s just a simplification for the purpose of the question). It only occupies a single byte – as opposed to char(1), which needs at least 2 bytes on disk and 5 in RAM.

Singleton row with any rare type

To restrict to a single row overall that does not have type ‘c’, use a partial unique index on an expression:

CREATE UNIQUE INDEX example_single_exception_idx ON example ((example_state <> 'c'))
WHERE example_state <> 'c';

Leave a Reply

Your email address will not be published. Required fields are marked *