CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mohamed
Mohamed

Reputation: 567

setting column to not null based on condition oracle

i have a table with column A and column B. I would like to make column A null-able if column B value is '123' and make it NOT NULL if column B is anything else.

Upvotes: 0

Views: 604

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

No such thing as conditional nullability. But, you can have a NULL value check:

create table t (
    . . . ,
    a int,
    b varchar2(255),
    constraint chk_t_a check ( (b <> '123' and a is not null) or (b <> '123'))
);

If b can take on NULL values, then you need to also take that into account.

Note: If you want a to always be NULL only when b = '123' then use:

create table t (
    . . . ,
    a int,
    b varchar2(255),
    constraint chk_t_a check ( not (b = '123' and a is not null) )
);

check constraints pass even when the resulting value is NULL -- unlike WHERE and CASE WHEN logic. So, this works even when b is NULL.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use case expression :

select (case when colb <> 123 then cola end) as cola, t.colb
from table t;

Upvotes: 0

Related Questions