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@gmail.com> wrote:
> On 12 May 2010 07:34, Mike Christensen <mike@kitchenpc.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
>