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.