Re: Weird unique constraint

> -----Original Message-----
> From: Mike Christensen [mailto:mike(at)kitchenpc(dot)com]
> Sent: Wednesday, May 12, 2010 3:10 AM
> To: Thom Brown
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: Weird unique constraint
>
> By golly you're right; maybe I should try this stuff before I
> email hundreds of people :)
>
> On Wed, May 12, 2010 at 12:00 AM, Thom Brown
> <thombrown(at)gmail(dot)com> wrote:
> > On 12 May 2010 07:34, Mike Christensen <mike(at)kitchenpc(dot)com> wrote:
> >>
> >> I have the following constraint which almost works:
> >>
> >> ALTER TABLE ingredientforms ADD CONSTRAINT
> >> ingredientforms_UniqueIngredientForm UNIQUE(IngredientId,
> >> FormDisplayName);
> >>
> >> However, I want to allow rows that have the same IngredientId
> >> FormDisplayName /iff/ FormDisplayName is null.  If
> FormDisplayName is
> >> not null, then it must be unique.
> >>
> >> 1, NULL
> >> 1, NULL
> >>
> >> Would be allowed.
> >>
> >> 1, 'Foo'
> >> 1, 'Foo'
> >>
> >> would violate the constraint.
> >>
> >> 1, 'Foo'
> >> 1, 'Bar'
> >>
> >> would be allowed.
> >>
> >> Any way to do this?  Insert performance is not an issue since the
> >> table is almost never changed..
> >>
> >> Mike
> >>
> >
> > What you've said you want to do looks like what you'd be
> allowed to do
> > anyway.  You're allowed duplicate values on a unique
> constraint if one
> > of the columns is null.
> > Regards
> > Thom
> >
>

And, the reason it works is that (1, NULL) IS NOT equal to (1, NULL), because NULL is never equal to NULL,
(result of comparing NULL to NULL is NULL, and not TRUE or FALSE).
So these 2 rows:

1, NULL
1, NULL

do not violate "uniqueness" of your constraint.

Regards,
Igor Neyman