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