"Andrus" <eetasoft@online.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