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