Search code examples
sqloracleconstraintscheck-constraints

Oracle SQL - can CASE be used in a check constraint to determine data attributes?

I'm using Oracle 10g and I want to apply a constraint to a table where the value entered for one column determines whether another column IS NULL or IS NOT NULL. Column1 can only contain 1 or 0; Column2 is VARCHAR2(255).

I know that the following works:

CONSTRAINT ck_1 CHECK ((col1=1 AND col2 IS NOT NULL) OR (col1=0 AND col2 IS NULL));

However, I was wondering if it is possible to use CASE to perform this constraint and set the attribute NOT NULL on col2, or can CASE only be used to define values? i.e. could something like this work:

CONSTRAINT ck_1 CHECK (CASE WHEN col1=1 THEN col2 IS NOT NULL ELSE col2 IS NULL END);
Solution

  • Since CASE expressions must return a value, and check constraints are boolean, you'll have to compare the result with something, e.g.:

    CONSTRAINT ck_1 CHECK (CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END = col1);