Re: UNIQUE and NULL in SQL

On 2 Jan 2002 09:56:47 -0000, Clive Page <cgp_at_nospam.le.ac.uk> wrote:

>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.
>
IMHO, these are examples of a semantic meaning allocated to null during database design and do not accurately represent the true state of null as a general concept. In any case, the OP is actually interested in the ANSI SQL implementation - and I can't talk about that with any real degree of confidence.

-- 
Mark A. Preston, The Magpie's Nest, Lancashire, UK
Email   : mark_at_magpiesnest.co.uk
Website : www.magpiesnest.co.uk