Re: unique constraint - bug?

Merrill Oveson <merrill(at)actarg(dot)com> writes:
> It appears as though the null value inserted for column b causes an
> abrogation of the unique constaint.

Two nulls are never considered equal, therefore the unique constraint
does not trigger.

This is correct behavior according to SQL92 4.10.2:

A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
^^^^^^^^
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.

(The second sentence just says that PRIMARY KEY implies NOT NULL as well
as UNIQUE.)

Another way to look at it is that the comparison to see whether the two
NULLs are equal would yield NULL, and a NULL result for a constraint
condition is not considered to violate the constraint.

Another way to look at it is that NULL means "I don't know what the
value is", so if you don't know what the values in two rows really are,
you don't know whether they're equal either. I suppose you could make
a case for either accepting or rejecting the UNIQUE constraint in that
situation --- but SQL92 chose the "accept" decision, and I think that
for the majority of practical applications they made the right choice.

If you don't like that behavior, possibly your column should be defined
as NOT NULL.

regards, tom lane