Re: UNIQUE and NULL in SQL

> 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.
> --

One can argue the correctness or incorrectness of a design that allows NULLS in a foreign key field.

And, as I said in my earlier post, one can get rid of the problem, by further normalizing. In my example,
if there were a separate table to record the PERSON_ID, and the SPOUSE_ID, one could deal with the
case of a single person by simply not adding any row for the person in this separate table. The non existence
of an entire row should not be open to any quarrels concerning interpretation.

So, up to this point, the difference between your opinion and mine is merely one of point of view. And there is a lot to be said for your point of view.

Where I would take issue with you is when you call "Not Applicable" a "value". It isn't a value, IMHO. It's a conclusion drawn from the fact that a value is not present at a given intersection of a row and a column.

And I would maintain that NULL is never a value, regardless of the conclusion drawn from it. And I would further maintain that this is not a matter of opinion, but a matter of definition.

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