Re: multi-column unique constraints with nullable columns

On Fri, 29 Apr 2005, Tornroth, Phill wrote:

> I have many tables who's natural key includes a nullable column. In this
> cases it's a soft-delete or 'deprecated' date time. I'd like to add a
> table constraint enforcing this constraint without writing a custom
> procedure, but I've found that postgres treats NULLs very consistently
> with respect to the NULL != NULL behavior. As a result, when I define a
> constraint on the last two columns in these insert statements... they
> both succeed.
>
> insert into mytable values (1,300, null);
> insert into mytable values (1,300, null);
>
> This is frustrating, and while there may be someone who actually wants
> constraints to work this way... I can't understand why.
>
> Now, I understand that the best way to solve my problem would be to use
> only non-nullable columns for my natural keys. I actually plan to do
> that, and use a very high value for my 'undeprecated' date to solve most
> of my problems related to this. However, I can't release that version of
> software carelessly and I need to tighten up customer databases in the
> meantime.
>
> Is there a way to get the behavior I want?

I believe you can add partial unique indexes to cover the case where a
column is null, but if you have multiple nullable columns you need to
worry about you end up with a bunch of indexes.

> Also, is this in compliance with SQL92? I'm surprised constraints work
> this way.

As far as we can tell, this is explicitly what SQL wants to happen. The
UNIQUE predicate (which the UNIQUE constraint is described in terms of)
is defined as "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
corresponding column in the other row according to Subclause 8.2 ... then
the result of the <unique predicate> is true; otherwise, the result of the
<unique predicate> is false."