Re: Null value gets past check constraint

In article <37A90D85.4156@earthlink.net>, jleffler@earthlink.net wrote: > Jacob Salomon wrote: -- SNIP -- >> 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"? > No; there's an implicit 'IF x IS NOT NULL AND (rest of constraint)' > in there. Thanks, Jonathan. That insight will help in the future. I am implementing (and naming) the separate NOT NULL constraints. Interesting, that I must define the NOT NULL constraint before the check constraint. This is a syntax issue and IMO not subject to useful arguments. Now a curiosity: Which will result in more efficient inserts & updates? Separate constraints for not null and check? Or a single check constraint that excludes nulls as well as non Y/N characters? Separate constraints: on_line_flag char (1) default 'N' not null constraint ean_ss_olf_nn check (on_line_flag in ("N", "Y")) constraint ean_ss_olf_yn, One compound constraint: on_line_flag char (1) default 'N' check( on_line_flag is not null and on_line_flag in ("N", "Y")) constraint ean_ss_olf_nnyn, In light of Jonatathan's insight, I suspect the multiple simple constraints will result in faster inserts (relevant when loading a million rows en masse). In fact, I am implementing it this way now (as soon as I can gain exclusive access to the table). But I'd like to hear from someone who has played with this stuff before. Thanks much. +---- Jacob Salomon - DBA JSalomon@bn.com -----------------------------+ |--------------- Obligatory sesquipedalian obfuscation: ---------------| | An object of igneous, sedimentary or metamorphic mineral in combined | | states of elevated linear and rotational kinetic energy acquires no | | accumulation of bryophytic vegetation. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.