On 12 May 2010 07:34, Mike Christensen <mike@xxxxxxxxxxxxx> 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
