NULLs in CHECK constraints

I have a question which I believe spans all present and future versions of Informix Dynamic Server, on all platforms. ;-) I am trying to install a CHECK constraint on a non-required ('null') field. The field MUST have a "Y" or a "N" in it - but only when it's present. I installed a check constraint that read "...CHECK (<fieldname> IN ('Y', 'N', NULL)), and guess what? It compiled, but had no effect - the value for this field remained unconstrained. Removing NULL from the list resulted in a correctly-working constraint, but not the constraint I wanted. Any ideas? Art? Rich -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com

Richard Auslander wrote: > > I have a question which I believe spans all present and future versions > of Informix Dynamic Server, on all platforms. ;-) > > I am trying to install a CHECK constraint on a non-required ('null') > field. The field MUST have a "Y" or a "N" in it - but only when it's > present. I installed a check constraint that read "...CHECK > (<fieldname> IN ('Y', 'N', NULL)), and guess what? It compiled, but had > no effect - the value for this field remained unconstrained. Removing > NULL from the list resulted in a correctly-working constraint, but not > the constraint I wanted. Any ideas? Art? YUP! ANY test against NULL, except "IS NULL" and "IS NOT NULL" is both true and false ie indeterminate. So the constraint as you stated it is no op. Why? The engine handles an IN list as 'fld="Y" or fld='N' or fld=NULL' but since it is a check for invalid data it is apparently being interpreted as 'fld!="Y" AND fld!="N" AND fld !=NULL' since ANY value for that column will fail the test "!= NULL" the test succeeds and and any value can be inserted. The correct check constraint to do what you want is: CHECK (<fieldname> IN ('Y', 'N') OR <fieldname> IS NULL) Art S. Kagel