Skip to content
Advertisement

SQL query to set CHECK constraint on a column based on the value in other column

I need to set a CHECK constraint on a column based on the value in other column (both within the same table)

Like when COLUMN A is NULL, COLUMN B should be NULL But if COLUMN A has values 1 or 2 or 3 then COLUMN B should definitely have a date value.

Advertisement

Answer

Define B as a date. And then use a check constraint:

create table t (
    . . .
    b date,
    constraint chk_t_b check (b is null or a not in (1, 2, 3)),
    . . .
);

If b is a string and you just want it to “look” like a date, then you can do something like:

create table t (
    . . .
    b date,
    constraint chk_t_b check (regexp_like(b, '^[0-9]{4}-[0-1][0-9]-[0-3][0-9]$') or a not in (1, 2, 3)),
    . . .
);

The date matching isn’t exact, but you haven’t specified the format for the “date” if the column really stores a string.

8 People found this is helpful
Advertisement