Re: Unique Constraint with Multiple NULLS

From: Troels Arvin <troels_at_arvin.dk>
Date: Wed, 07 Jan 2004 10:38:40 +0100
Message-ID: <pan.2004.01.07.09.38.40.91621@arvin.dk>

On Thu, 01 Jan 2004 21:48:24 +0000, David Portas wrote:

>> What is the SQL-92 standard for allowing multiple NULL values within a
>> unique constraint?

>
> A UNIQUE constraint treats NULLs in the same column as equal to each other.
> In other words UNIQUE(colX) will not be satisfied if colX contains more than
> one NULL value.

I believe that you are wrong about this. Where have you read that?

From section 8.9 in [1]:

If there are no two rows in T such that the value of each column in one row is non-null and is equal to the value of the corresponding column in the other row according to Subclause 8.2, "<comparison predicate>", then the result of the <unique predicate> is true; otherwise, the result of the <unique predicate> is false.

Loose summary from page 362 in [2] (section "Column Constraints and Table Constraints"):

SQL-89 required that a UNIQUE constraint be accompanied by a NOT NULL constraint. SQL-92 relaxed that requirement, so that you may specify UNIQUE without NOT NULL, and SQL-1999 hasn't changed that. The UNIQUE check excludes rows where the checked column(s) have a 'value' of NULL. References:
1: A late draft of SQL-92:
ftp://sqlstandards.org/SC32/WG3/Progression_Documents/IS/iso-9075-1992.pdf Currently down, it seems. However, late SQL-92 drafts are easily found through a search engine.

2: Page 362 in the book
http://books.elsevier.com/uk/mk/uk/subindex.asp?isbn=1558604561

-- 
Greetings from Troels Arvin, Copenhagen, Denmark

Received on Wed Jan 07 2004 - 03:38:40 CST