CODEWITHSUNDEEP
sqloracle-databaseconstraints
samg
samg

Reputation: 323

Oracle: Conditional not-null constraint when both conditions are true

I have to alter a table to create a conditional not null constraint on a specific column so that it cannot be null if col1 and col2 have values.

First, what is a conditional not null constraint?

Second, can you please provide a syntax on how to accomplish such thing?

Upvotes: 4

Views: 3609

Answers (2)

Marmite Bomber
Marmite Bomber

Reputation: 21063

The predicate you need to check is

if (col1 is not null and col2 is not null) then specific is not null

The predicate if A then B can be writen as not A or B

Note that the precendence is (not A) or B see the discussion here

So you get:

alter table spec add constraint my_spec
check (not (col1 is not null and col2 is not null) or specific is not null);

if col1 or col2 is null spcific is nullable

insert into spec(col1,col2,specific) values(null,null,null);
insert into spec(col1,col2,specific) values(1,null,null);
insert into spec(col1,col2,specific) values(null,1,null);

if both col1 and col2 are defined secific in NOT NULL

insert into spec(col1,col2,specific) values(1,1,1);
insert into spec(col1,col2,specific) values(1,1,null);
-- ORA-02290: check constraint (REPORTER.MY_SPEC) violated

On existing table you can add this check only if the existing data fullfill the validation, otherwise you get this exception

 ORA-02293: cannot validate (OWNER.MY_SPEC) - check constraint violated

To find the rows that violated the new rule simply query

 select * from spec
 where NOT (not (col1 is not null and col2 is not null) or specific is not null);

You must either delete those rows or set specific to a non NULL value or set col1 or col2 to NULL.

Alternatively you may enable the constraint with NOVALIDATE option, this tolerates the existing violations, but enforce that new changes follow the constraint.

alter table spec add constraint my_spec
check (not (col1 is not null and col2 is not null) or specific is not null)
enable novalidate;

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269563

This sounds like a check constraint:

alter table t add constraint chk_t_specific
    check ( (col1 is null) or (col2 is null) or (specific is not null) );

This can also be written as:

alter table t add constraint chk_t_specific
    check (not ( (col1 is not null) and 
                 (col2 is not null) and
                 (specific is null)
               ) 
          );

If that helps you follow the logic better.

This evaluates to false only under the following conditions:

  • col1 is not null (i.e. col1 does have a value)
  • col2 is not null (i.e. col2 does have a value)
  • specific is null

This is the exact condition that the OP wants to filter out.

On an existing table, you can see what rows violated the constraint:

select *
from t
where col1 is not null and col2 is not null and specific is null;

If any rows violated the constraint, you need to fix them before adding the constraint.

Upvotes: 3

Related Questions