Search code examples
sqloracleddlcheck-constraints

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.

Solution

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