Re: UNIQUE and NULL in SQL

From: --CELKO-- <71062.1056_at_compuserve.com>
Date: 16 Jan 2002 10:54:46 -0800
Message-ID: <c0d87ec0.0201161054.1c91d61@posting.google.com>

>> What is the rationale for having a UNIQUE constraint treat NULLS as
equal? <<

The same model we used for GROUP BY, which puts NULLs into a single group and does not make each one a group of its own. Grouping is different from equality in SQL and does not have the same rules. The property that a grouping on a table with UNIQUE constraints on the unique constraint column(s) will produce groups of cardinality one is fairly useful.

In English that means:

CREATE TABLE Foobar(a INTEGER UNIQUE);

INSERT INTO Foobar VALUES (1);
INSERT INTO Foobar VALUES (2);
INSERT INTO Foobar VALUES (NULL);

SELECT a, COUNT(*) AS occurs
  FROM Foobar;

a occurs

---

1 1
2 1
NULL 1 Received on Wed Jan 16 2002 - 12:54:46 CST