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

Question

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

Accepted Answer

`null`s 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')
```

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

Solution

Problem