3

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

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Owen
  • 1,652
  • 2
  • 20
  • 24
  • 1
    Try `ADM1 IS NULL`. Thou shall not compare to `NULL` with `=` operator in SQL. – npe May 28 '14 at 19:34

2 Answers2

6

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')
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Well, that was simple :). Worked perfectly. (just pending the 10 minute minimum to accept). Thanks a tonne. – Owen May 28 '14 at 19:36
2

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?

TommCatt
  • 5,498
  • 1
  • 13
  • 20
  • Good solution with FK but there is no such disadvantage. No need to join to retrieve the contents. The field contains 'Central', 'East', etc. as strings and the list of allowed strings are in the lookup table. – fredt May 30 '14 at 19:18
  • Well, I just assumed that a key value would be used instead of the full text of the location. It could be a surrogate key or something like 'C', 'E', 'FW', 'MW', 'W'. However, if the text itself was used as the key, then, yes, you would have referential integrity without having to join. However, if you did it that way and ever wanted to make any changes to the text ('East' => 'Eastern') then you have a needlessly tricky problem. – TommCatt Jun 01 '14 at 05:23
  • You *could have* a tricky problem. You could cascade such a change, but many DBAs don't like that as automatic cascades can severely effect a database. – TommCatt Jun 01 '14 at 05:29