Wednesday, June 21, 2006

NULL in SQL

Should a null QString be identical to an empty QString? Is there sanity in having undefined vs empty strings in PERL? What about null vs empty strings in Java?

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:

James Ots said...

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))

Clarence Dang said...

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.