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