Oracle SQL - check constraint

user_13475787 0
Viewed 220

This check constraint isn't working for me:

ALTER TABLE tab1
ADD CONSTRAINT CHK1 CHECK 
(col1 in ('val1','val2','val3','val4') and (col2='0' or col2 IS NULL))
ENABLE;

What I need is if col1 contains any of the mentioned 4 values, then col2 has to be '0' or 'NULL'.

check-constraints ddl oracle sql
4 Answers

Assuming col1 is nullable:

ALTER TABLE tab1
ADD CONSTRAINT CHK1 CHECK 
(col1 not in ('val1','val2','val3','val4') or (col1 is not null and (col2='0' or col2 IS NULL)))
ENABLE;

If it's not nullable then you can take out the col1 is not null but it's not going to be too important.

The constraint now means: If col1 isn't in those values then it's fine. But if it is, then the other side of the condition must be met.

user_98561760

You can write this as:

ALTER TABLE tab1
    ADD CONSTRAINT CHK1 
        CHECK (col1 NOT IN ('val1', 'val2', 'val3', 'val4') OR
               col2 <> '0'
              )

This can be equivalently written as:

    CHECK (NOT (col1 IN ('val1', 'val2', 'val3', 'val4') AND
                col2 <> '0'
               )
          )

These both allow values other than the four specified values for col1 with no restriction on col2.

user_11440350

You need to write it as follows:

ALTER TABLE tab1 ADD CONSTRAINT CHK1 
    CHECK (col1 NOT IN ('val1', 'val2', 'val3', 'val4') OR col2 = '0')
ENABLE;

Db<>fiddle

user_115656290

I think your current should work properly as col2 has numeric data type, except quotes wrapping up zero ('0') is redundant within the constraint, and be aware that the values of col1 listed within the constraint are to be inserted into the table without whitespaces. So, you might use the current check constraint, even briefly with a slight change through use of NVL() function as below :

ALTER TABLE tab1
ADD CONSTRAINT CHK1 
CHECK(col1 IN ('val1','val2','val3','val4') AND NVL(col2,0)=0)
ENABLE;

Demo

user_58413060
Related