Re: Null value gets past check constraint

---- you 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? >;-) Hey dude, you need to read up on NULLs. A NULL could be a 'N', it could be a 'Y'. It could be any value, because they mean 'Unknown'. > 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? No one is forcing you to do anything, but if you want to avoid NULLs, then you need a NOT NULL constraint dude. AB ---------------------------------------------------------------- Get your free email from AltaVista at http://altavista.iname.com

And to take this just a little farther... A "null" value IS NOT a space. The value could be anything other than the N or Y you are looking for, or the value could be missing. Each engine (7.23 or 7.30) treats null values just a little different. I've found it is best (if possible) to not allow nulls in cols and to always check for nulls AND SPACES. a_blonde@mindless.com wrote: > > ---- you 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? >;-) > > Hey dude, you need to read up on NULLs. A NULL could be a 'N', it could be a 'Y'. It could be any value, because they mean 'Unknown'. > > > 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? > > No one is forcing you to do anything, but if you want to avoid NULLs, then you need a NOT NULL constraint dude. > > AB > ---------------------------------------------------------------- > Get your free email from AltaVista at http://altavista.iname.com