Search code examples
sqlhsqldbddlcheck-constraint

How can I write a CHECK constraint in SQL that allows a series of strings OR a blank value?

I am trying to write a check constraint for a field in SQL that allows either a series of strings (in this case province names) or a blank value. Current code is this:

ALTER TABLE CODEDLOCATION
ADD CHECK (ADM1='Central' OR ADM1='East' OR ADM1='Far-Western' OR ADM1='Mid-Western' OR ADM1='West')

Which works for the ADM1 field but causes an error if there is a blank/null value for the ADM1 field in a new record. I have tried the following two options but neither works:

ALTER TABLE CODEDLOCATION
ADD CHECK (ADM1='' OR ADM1='Central' OR ADM1='East' OR ADM1='Far-Western' OR ADM1='Mid-Western' OR ADM1='West')

ALTER TABLE CODEDLOCATION
ADD CHECK (ADM1=null OR ADM1='Central' OR ADM1='East' OR ADM1='Far-Western' OR ADM1='Mid-Western' OR ADM1='West')

Using HSQLDB with OpenOffice Base. Thanks!

P.S. The reason i'm using checks instead of a foreign key constraint here is related to some challenges working with OO Base forms...

Solution

  • nulls are evaluated with the is operator, not the = operator:

    ALTER TABLE CODEDLOCATION
ADD CHECK 
(ADM1 IS null OR 
 ADM1 = 'Central' OR 
 ADM1 = 'East' OR 
 ADM1 = 'Far-Western' OR 
 ADM1 = 'Mid-Western' OR 
 ADM1 = 'West')