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?
From a pragmatic point of view, I have never had a case where I wanted NULLs to be treated equal in UNIQUE constraints, but I have come across many cases where I wanted the NULLs to not be counted in the UNIQUE constraint.

As for why I think it should be the other way:

In SQL, a table/column constraint is violated when it's condition is FALSE. In the case of a UNIQUE constraint, the condition is "[x] NOT IN (SELECT [column] FROM [table])". Now, "NULL NOT IN (SELECT [column] FROM [table])" will never be TRUE or FALSE; it is UNKNOWN. Therefore, the constraint should not be considered violated since it didn't evalutate to FALSE. Received on Mon Dec 31 2001 - 04:04:56 CET