Re: UNIQUE and NULL in SQL

Andreas Bauer <baueran_at_in.tum.de> wrote in message news:MuEX7.7635$ny3.1201964084_at_news.tli.de...
> On Sun, 30 Dec 2001 03:28:12 +0000, Brian Smith wrote:
>
> > What is the rationale for having a UNIQUE constraint treat NULLS as
> > equal?
>
> What would be the rationale behind UNIQUE if it was any different to
> this? That is, what makes the first NULL different to your second
> NULL?
No offense, but the poster of original question has a very legitimate point in questioning the non-uniqueness of nulls, though more from a practical angle rather than a theoretical one. The rationale of having DB vendors and standards committees treat nulls as the original poster suggests, especially in terms of UNIQUE, would be that the definition of NULL would become more in line with the definition of NULL in set and relational theory.

 A NULL should evaluate to some truth value that is unknown. If you don't know the value of two employee's middle name, do you assume the value of the two middle names are the same? According to the way nulls are specificied in the standards comittee, and implemented in commercial database systems, we do, at least semantically, assume that nulls not only are values, but are or can be equivalent to each other. An implementation that is at loggerheads with the theoretical concept of null.

In a set, a NULL cannot and does not equal another NULL; and by extension, since no two NULLs can be equal, tuples with all attribute values exactly the same, but with the presence of one or more NULLS in an arbitrary number of attributes would never theoretically be duplicates.

Granted, SQL and relational database implementation broke from this from nearly the beginning, and vendors have always therefore treated the NULL as a scalar value. So, I guess such implementations are now a defacto standard in terms of system implementation. I still find issue with it from the theoretical standpoint though.

Dan Received on Sun Dec 30 2001 - 19:47:01 CET