Oracle SQL - check constraint

Question

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'.

is the data type of `col2` non-numeric? If so, that should contain some whitespaces, since the constraint seems to be flawless for an integer value(except for redundant quotes wrapping up zero). — Barbaros Özhan, Dec 16 '20 at 15:44
Btw, make sure `col1` also shouldn't contain whitespaces for those values('val1','val2'...) — Barbaros Özhan, Dec 16 '20 at 15:49
col2 is "number" datatype and col1 is varchar with about 14 different values of which oly 4 I am concerned of for this particular constraint — sp123, Dec 16 '20 at 15:56
select * from tab1 where (col1 in ('val1','val2','val3','val4') and (TAX_AMOUNT!='0' and TAX_AMOUNT IS NOT NULL)); - This returns 0 records, so theres no rows violating the constraint I am trying to add right? — sp123, Dec 16 '20 at 15:58

score 2 · Answer 1 · answered Dec 16 '20 at 15:38

2

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.

answered Dec 16 '20 at 15:38

Andrew Sayer

2,296
1
7
9

My constraint should check col2 is either '0' or 'NULL' if col1 contains any of the 4 values. Reverse doesn't hold true, Values other then these 4 in col1 can also contain 0 in col2 – sp123 Dec 16 '20 at 16:07
That’s exactly what the code I’ve shared will do, try it with your test cases – Andrew Sayer Dec 16 '20 at 17:52

Gordon Linoff · Answer 2 · 2020-12-16T16:10:15.747

1

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.

edited Dec 16 '20 at 16:10

answered Dec 16 '20 at 15:43

Gordon Linoff

1,242,037
58
646
786

My constraint should check col2 is either '0' or 'NULL' if col1 contains any of the 4 values. Reverse doesn't hold true, Values other then these 4 in col1 can also contain 0 in col2 – sp123 Dec 16 '20 at 16:07
@sp123 . . . These should guarantee that you never have the four values in `col1` with anything other than `'0'` or `NULL` in `col2`. – Gordon Linoff Dec 16 '20 at 16:10

score 0 · Answer 3 · answered Dec 16 '20 at 15:51

0

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

answered Dec 16 '20 at 15:51

Popeye

35,427
4
10
31

My constraint should check col2 is either '0' or 'NULL' if col1 contains any of the 4 values. Reverse doesn't hold true, Values other then these 4 in col1 can also contain 0 in col2 – sp123 Dec 16 '20 at 16:07
I think you did not try it. See fiddle with different value than listed and 0 in col2. https://dbfiddle.uk/?rdbms=oracle_18&fiddle=908ffc9e77dba2fe8f826aeb96b19746 – Popeye Dec 16 '20 at 17:17

score 0 · Answer 4 · answered Dec 16 '20 at 16:28

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

Oracle SQL - check constraint

4 Answers4