Re: UNIQUE and NULL in SQL

From: Brian Smith <brian-l-smith_at_uiowa.edu>
Date: 30 Dec 2001 19:04:56 -0800
Message-ID: <60360d48.0112301904.3f4a226f@posting.google.com>

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 Sun Dec 30 2001 - 21:04:56 CST