Re: UNIQUE and NULL in SQL

In article <4fdv2ugs6u4m8et1pr5bb7sbsmrsmdu350_at_4ax.com>, Mark Preston <mark_at_mpreston.demon.co.uk> wrote:

>Nulls are "unknown value" - not zero or the empty string, but unknown.
>Using unique on a null field is just silly - it is meaningless.

I guess that is true as nulls are implemented in SQL. But I recall reading a paper some years ago which suggested at least five different possible meanings of "null" that one might want to distinguish. For example, if you have a simple table with name, address, and telephone number. The phone number field might be null for a variety of different reasons, e.g.

• person does not have a phone
• person has a phone but the number is ex-directory
• person is moving house, number is temporarily unavailable
• number was accidentally not recorded by the data entry system
• person has several phones, for details of which one to use at which time see some other field.

You might well want to distinguish between these meanings of null. Obviously if using SQL you can't use the simple null facility built-in, but you might want to build your own flag system.

-- 
Clive Page   cgp_at_le.ac.uk