UNIQUE and NULL in SQL

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

How can I create a UNIQUE constraint on a column that allows multiple rows to have NULL for that column:

CREATE TABLE x ( x NUMBER UNIQUE);

INSERT INTO x VALUES (1);     -- SUCCEESS
INSERT INTO x VALUES (1);     -- FAILURE
INSERT INTO x VALUES (NULL);  -- SUCCESS
INSERT INTO x VALUES (NULL);  -- FAILURE

I want the fourth insert statement above to succeed while still enforcing the UNIQUE constraint for non-null columns. Is that possible?

Thanks,
Brian Received on Sat Dec 29 2001 - 21:28:12 CST