Re: UNIQUE and NULL in SQL

> 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 agree with you on the fundamental point, that uniqueness is not meaningful with regard to NULLS.

But my understanding of NULL is slightly different from yours.

IIRC, NULL indicates "no value present". One possible interpretation is "unknown value". But it isn't the only possible interpretation. Another possible interpretation is: "not applicable".

For example, you could have two adjacent columns, named "MARITAL_STATUS" and "SPOUSE_ID", in some table of people.

If "MARITAL_STATUS" is "SINGLE" , suitably encoded, then the right value for "SPOUSE_ID" is NULL.
But this doesn't mean that the spouse Id is unknown. It is known not to exist. The need for this NULL can be
eliminated by further normalization. But sometimes designers prefer to use NULLs over adding one more table.

--
Regards,
    David Cressey
    www.dcressey.com
"Mark Preston" <mark_at_mpreston.demon.co.uk> wrote in message
news:4fdv2ugs6u4m8et1pr5bb7sbsmrsmdu350_at_4ax.com...