2

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.

CREATE TABLE example
(
  example_id    serial PRIMARY KEY,
  example_state CHAR(1) NOT NULL
);

ALTER TABLE example ADD CONSTRAINT 
  example_constraint
CHECK (example_state = 'a' OR example_state = 'b' OR example_state = 'c');
  • Your question is logically impossible. Once you have "all rows be of example_type 'c'" there are no other rows that can be 'a' of 'b'. Please clarify. Do you want to allow 0-n rows of type 'c' and only a single row with type in ('a', 'b') - or 0-1 rows with 'b' and 0-1 rows with 'a'? – Erwin Brandstetter Aug 4 '14 at 17:15
  • Corrected my wording. Thanks for the answer; looks like the 'Singleton' answer was what I was looking for. – Nathan Lippi Aug 4 '14 at 20:02
  • 1
    Thanks for clarifying. And that's actually a very interesting question. – Erwin Brandstetter Aug 4 '14 at 20:03
4

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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.