UNIQUE and NULL in SQL

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 Sun Dec 30 2001 - 04:28:12 CET