Re: Unique Constraint with Multiple NULLS
Date: Wed, 07 Jan 2004 10:38:40 +0100
Message-ID: <pan.2004.01.07.09.38.40.91621_at_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, DenmarkReceived on Wed Jan 07 2004 - 10:38:40 CET