CODEWITHSUNDEEP
oracle-databasecheck-constraints
Thomas Carlton
Thomas Carlton

Reputation: 5968

How to create advanced check condition

I have an Oracle table and I would like to create a check condition like this : 

ALTER TABLE MyTable
    ADD CONSTRAINT MyTable_CHK2 CHECK (
    case Dimension 
        When 1 then 
            nvl(dimensiontype1,-1)<>-1
        when 2 then 
            nvl(dimensiontype1,-1)<>-1 and nvl(dimensiontype2,-1)<>-1
        when 3 then 
            nvl(dimensiontype1,-1)<>-1 and nvl(dimensiontype2,-1)<>-1 and nvl(dimensiontype3,-1)<>-1
        else
            true
        end
    )
disable

The query is not working. I'm having the error : Missing Keyword.

Anyone know how to solve that please ?

Thanks.

Upvotes: 0

Views: 102

Answers (2)

Rene
Rene

Reputation: 10541

Check constraint should be:

(dimension=1 and dimensiontype1 is not null)
or (dimension=2 and dimensiontype1 is not null and dimensiontype2 is not null)
or (dimension=3 and dimensiontyp1 is not null and dimensiontype2 is not null and dimensionType 3 is not null)

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

You probably want an AND / OR expression

 ALTER TABLE MyTable
    ADD CONSTRAINT MyTable_CHK2 CHECK 
 (
    (  Dimension  =  1 and  nvl(dimensiontype1,-1)  <> - 1 ) OR
    (  Dimension  =  2 and  (nvl(dimensiontype1,-1) <> - 1 and nvl(dimensiontype2,-1)<> -1 ) ) OR
    (  Dimension  =  3 and  (nvl(dimensiontype1,-1) <>  -1 and nvl(dimensiontype2,-1)<>  -1 and nvl(dimensiontype3,-1) <> -1))
) disable  ;

Upvotes: 4

Related Questions