Re: Weird unique constraint

From: Thom Brown <thombrown(at)gmail(dot)com>
To: Mike Christensen <mike(at)kitchenpc(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Weird unique constraint
Date: 2010-05-12 07:00:06
Message-ID: AANLkTiljQ62r63z4Eg9gLPgIfxQqiC5quYi7LF1AY5xZ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message A. Kretschmer 2010-05-12 07:01:48 Re: Weird unique constraint
Previous Message Thom Brown 2010-05-12 06:55:46 Re: Weird unique constraint