CODEWITHSUNDEEP
sqloracle-databaseddlcheck-constraints
sp123
sp123

Reputation: 47

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.

Upvotes: 0

Views: 1229

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

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.

Upvotes: 0

The Impaler
The Impaler

Reputation: 48850

The CHECK constraint you want could take the form:

alter table t add constraint chk1
  check (a is null and b is null or a in (1, 2) and b is not null);

Upvotes: 1

Related Questions