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

Question

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

Is there a way to write a check constraints based on values of particular columns?

Erwin Brandstetter · Accepted Answer · 2014-05-25 12:25:58Z

13

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).

669k160 gold badges1.2k silver badges1.3k bronze badges

answered May 25, 2014 at 6:09

Eevee

48.7k11 gold badges100 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1