Re: NULLs in CHECK constraints

Richard NULL will match to any value. You can try installing a check constraint ("Y","N","") but of course "" is not NULL either. You may have to change your SELECT statements from WHERE colname IS [NOT] NULL to WHERE colname [!]= "". HTH Sujit Richard Auslander <rich@airflash.com> on 09/13/99 07:24:43 PM Please respond to Richard Auslander <rich@airflash.com> To: informix-list@iiug.org cc: (bcc: Sujit Pal) Subject: 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