Weird unique constraint

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

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thom Brown 2010-05-12 06:55:46 Re: Weird unique constraint
Previous Message A. Kretschmer 2010-05-12 05:23:31 Re: Pulling data from a constraint def