It's those annoying cases in programming that always require extra checks. In SQL, it's NULL. Comparisons with it almost always return "unknown" (which is usually interpreted as false). Worse still, "NOT unknown" is still "unknown". So when one writes:
CHECK (attribute = 0 OR attribute = 1)
SQL will happily allow the insertion of a row with a NULL "attribute". So this must be rewritten as:
CHECK (attribute IS NOT NULL AND (attribute = 0 OR attribute = 1))
See, I just dropped a few marks for this tiny mistake...
Oh, and BTW, Oracle morphs empty strings into NULLs, which is the pinnacle of insane behaviour.
2 comments:
It'd probably be better to define the column as being not null in the first place:
CREATE TABLE BOB (
THING INTEGER NOT NULL,
CONSTRAINT CK_BOB_THING_BOOL CHECK (THING = 1 OR THING = 0))
Yes, I was not allowed the change the given schema.
In any case, the problem with "NOT NULL" is that Oracle converts all empty strings to NULLs.
Post a Comment