NOT NULL constraint on a column when another column has a particular value

check-constraints, postgresql

Solution

You can write a table-level constraint, sure.

CREATE TABLE test (
    col1 VARCHAR(20),
    col2 VARCHAR(20),
    CHECK (col1 != '1' OR col2 IS NOT NULL)
);

Either `col1` isn't `'1'` (and col2 can be anything), or `col1` is `'1'` (and `col2` can't be null).

See the third example in the manual.

Problem

``` create table test ( col1 varchar(20), col2 varchar(20) ) ``` - When col1 has value '1', col2 cannot be null. - When col1 has any other value, col2 can be null. Is there a way to write a check constraints based on values of particular columns?

Original source