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

sql oracle constraints check-constraints
blizzard242 picture blizzard242 · Nov 9, 2011 · Viewed 7.7k times · Source

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

Answer

DCookie picture DCookie · Nov 10, 2011

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

Related questions

CHECK CONSTRAINT in Oracle SQL

I have the following table Goods_In_Wagon(Goods_ID,Wagon_ID,Total_Weight). I need to create a number of if statement check constraint that says "IF WAGON_ID is between 90 and 99 THEN Total_Weight must be greater than 10." …

Read More
Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Oracle find a constraint

I have a constraint called users.SYS_C00381400. How do I find what that constraint is? Is there a way to query all constraints?

Read More