CODEWITHSUNDEEP
sqlhsqldbddlcheck-constraint
Owen
Owen

Reputation: 1662

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

Upvotes: 3

Views: 7477

Answers (2)

TommCatt
TommCatt

Reputation: 5636

From a strictly modeling pov, the best solution would be a lookup table and make the ADM1 field a foreign key to it. An FK field may be null but if not, must refer to a defined entry in the lookup table: 'Central', 'East', etc.

The advantages are:

  • If you have, or will have, another field of this same domain, it too can be defined as an FK. You don't have to cut/paste the check constraint from this field.
  • If you must make changes to the list of valid values, you just have to update the lookup table. Otherwise you have to go around to all the ADM1 fields and issue an Alter Table statement to change the constraint. Then (depending on the DBMS) you may to recompile triggers and SPs that refer to the tables you have just changed. Plus the fact that, normally, the Alter Table command is reserved to the DBAs while your developers should be able to perform Insert/Update.

A disadvantage some might mention would be that you have to perform a join to retrieve the contents of the field. Rarely is that enough of an impact to even consider a problem, especially in a small lookup table. Joins are to RDBMSs like compiling is to programming development. You can work around it if you really wanted to but why would you want to?

Upvotes: 2

Mureinik
Mureinik

Reputation: 311198

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

Upvotes: 7

Related Questions