Null value gets past check constraint
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?
Thanks.
--
+---- Jacob Salomon DBA JSalomon@bn.com --------------------+
|(In perpetual pursuit of undomesticated semi-aquatic avians)|
| The expedient performance of a task with excessive concern |
| regarding its duration-to-completion engenders a virtual |
| certainty of diminished benefit therefrom. |
| -- Benjamin Franklin (but he said it in 3 words) |
+------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Am I glad you brought this up! It forcd me to verify the behavior
myself. I knew those CHECKS were good for nothin'!
Here's a quote from the online docs:
"...all conditions that compare a null result in a null.."
"If you use any other operator with nulls and the result depends on the
value of the null, the result is UNKNOWN. Because null represents a
lack of data, a null cannot be equal or unequal to any value or to
another null. "
So your suspicion is right. Adding the NOT NULL column constraint,
in addition to the check, should work, even though you don't want to.
The DEFAULT doesn't help, because it only works on INSERT, not UPDATE.
If anyone else knows a way to not have to add the additional NOT NULL
constraint, I would like to hear it...
Candace
In article <7oakjh$8jf$1@nnrp1.deja.com>,
JSalomon@bn.com 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?
>
> Thanks.
> --
> +---- Jacob Salomon DBA JSalomon@bn.com --------------------+
> |(In perpetual pursuit of undomesticated semi-aquatic avians)|
> | The expedient performance of a task with excessive concern |
> | regarding its duration-to-completion engenders a virtual |
> | certainty of diminished benefit therefrom. |
> | -- Benjamin Franklin (but he said it in 3 words) |
> +------------------------------------------------------------+
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
holman@fas.harvard.edu wrote: [SNIP] > So your suspicion is right. Adding the NOT NULL column constraint, > in addition to the check, should work, even though you don't want to. > The DEFAULT doesn't help, because it only works on INSERT, not UPDATE. > > If anyone else knows a way to not have to add the additional NOT NULL > constraint, I would like to hear it... [Jake's original post SNIPPED for brevit sake and because we are a little off topic here.] With reference to using DEFAULT to prevent NULLS instead of a NOT NULL constraint. As noted this will work for INSERT but not UPDATE. To cover the UPDATE problem you COULD use an ON UPDATE OF trigger to force the default if that column is updated to NULL. This will silently and secretly override any fool who explicitely tries to update the column to NULL. Expensive and redundant considering that this is the purpose of a NOT NULL constraint and besides why do the FIXUP secretly like that? It will only end up with a line of fools behind your desk asking why every time they try to update the rows in that table the change does not take. What's wrong with Informix anyway? The Oracle DBA's servers don't ever lose updates like that! ;-b Art S. Kagel