Re: How to create unique constraint on NULL columns

"Andrus" <eetasoft(at)online(dot)ee> writes:
> CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR,
> UNIQUE (col1, col2) );

> This table allows to insert duplicate rows if col2 is NULL:

> INSERT INTO test VALUES ( '1', NULL );
> INSERT INTO test VALUES ( '1', NULL );

> does NOT cause error!

> How to create constraint so that NULL values are treated equal and second
> insert is rejected ?

Rethink your data design --- this behavior is required by the SQL
standard. A unique constraint is defined in terms of a "unique
predicate", which is defined as

2) 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 cor-
responding column in the other row according to Subclause 8.2,
"<comparison predicate>", then the result of the <unique predi-
cate> is true; otherwise, the result of the <unique predicate>
is false.

(SQL92 8.9 <unique predicate> general rule 2)

In general NULL should be used to mean "I don't know the value of this
field", not as a special value.

regards, tom lane