Re: Weird unique constraint

In response to Mike Christensen :
> 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.
>

test=# \d mike
     Table "public.mike"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer |
 t      | text    |


test=# create unique index idx_mike_unique on mike (id, t) where t is not null;
CREATE INDEX
test=*# insert into mike values (1, null);
INSERT 0 1
test=*# insert into mike values (1, null);
INSERT 0 1
test=*# insert into mike values (1, 'Foo');
INSERT 0 1
test=*# insert into mike values (1, 'bar');
INSERT 0 1
test=*# insert into mike values (1, 'Foo');
ERROR:  duplicate key value violates unique constraint "idx_mike_unique"


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99