Re: Weird unique constraint

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