Re: peeve - NULL testing results
From article <1srfa8INN2mt@emory.mathcs.emory.edu>, by jparker@hpbs2561.boi.hp.com (Jack Parker):
>
> So I have learned today that NULL means unknown. I am hard pressed to think
> of an occasion where this capability might have value to me, perhaps in an
> very strict integrity situation. (If a = NULL (unknown) then
> CALL WARN_SUPERVISOR_THAT_JOHN_ISN'T_FILLING_IN_ALL_OF_THE_FIELDS!())
>
We find the NULL useful for referential integrity:
CREATE TABLE person
( name CHAR (40) NOT NULL, { UNIQUE? Hmmm... }
id SERIAL NOT NULL PRIMARY KEY );
CREATE TABLE auto
( vin INTEGER NOT NULL UNIQUE,
owner INTEGER REFERENCES person (id) );
In this case, we cannot put 0 in the auto.owner field to indicate
an unregistered car, since that would violate the reference to the
person table. However, we want to keep the reference to ensure the
integrity of the database (it's unacceptable to arrest the wrong
person due to a typing error). However, we *can* use a NULL value
as the owner until such time as the car is registered.
The above example is somewhat contrived (presumably all cars are
owned even during manufacture), but is analogous to what we are
really doing; there are times when some information is unknown,
but when it is known, it has to be correct.
Andrew Shaw
AT&T Bell Labs
andrew.shaw@att.com // amazingly, unique!