Re: UNIQUE and NULL in SQL

On Mon, 31 Dec 2001 13:11:25 GMT, "David Cressey" <david_at_dcressey.com> 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 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".
>
That is an interpretation, true - but IMHO it is a quite incorrect one. By specifying that NULL is equivalent to (for example) "not applicable", you are assigning a KNOWN value to the unknown value content. This is not correct data management - if you know the value (in this case "not applicable") then a suitable and ALSO KNOWN value should be used rather than NULL.

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