Re: Null value gets past check constraint

Jacob Salomon wrote: > > Hi Family. > > I have a wierd (but suspicously familiar) situation. > > I have added check constraints to a bunch of columns in a table: > > alter table ean_stuff modify > ( book_in_hand_flag char (1) default 'N' > check (book_in_hand_flag in ("N", "Y")) > constraint ean_ss_bihf_yn, > on_line_flag char (1) default 'N' > check (on_line_flag in ("N", "Y")) > constraint ean_ss_olf_yn, > );> > On a nagging suspicion, I had a colleague update a row and try to stuff > a null into one of those columns in a row. > > It succeeded! Big time grumble! > > Since a null is obviously not "Y" and not "N", how did it get past the > guards? Is the constraint-checking code checking for "unequal"? > > I know that if I check for column != null it will still be NO, which > misleadingly looks like saying column == null . But that would be a > silly to check for compiance with the constraint! (wouldn't it? >;-) > > I also know I can add an additional NOT NULL constraint and get around > the problem. I would rather not - the more constraints I impose the > longer it takes to insert a row. But why should I have to anyway? > > Why doesn't the check constraint already exclude null values? This is the age old discussion of NULL values. Hasn't this subject been beaten to death here on this very news group? A NULL is an unknown value, so can't be compared to anything except NULL. That is what NOT NULL constraints are for. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock http://www.informix.com |//////// /| | mailto:mdstock@mydas.freeserve.co.uk |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |What year 2000 bug? year 2000 bug? |/// / ////| | |year 2000 bug? year 2000 bug? year |// / /////| | |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+