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

Question

create table test (
    col1 varchar(20),
    col2 varchar(20)
)

1. When col1 has value '1', col2 cannot be null.
2. 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?

Powel

Answer 1

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.

Marte