Thread: Weird unique constraint
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
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
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
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
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 >
On 12 May 2010 08:09, Mike Christensen <mike@kitchenpc.com> wrote:
Nah, gives us something to read :)
Thom
By golly you're right; maybe I should try this stuff before I email
hundreds of people :)
Nah, gives us something to read :)
Thom
Not to mention teaching some of us something we didn't know.
RobR, who probably should know a lot more about PostgreSQL than he does
If you think about it, NULL is not a value. It makes sens that it allows multiple NULLs. If you don't agree, than your SQL point of view doesn't match the majority, and your designs won't fit in the SQL paradigm . I think it is just a matter of experimenting, and experience to see how useful it is :) hth.
> -----Original Message----- > From: Mike Christensen [mailto:mike@kitchenpc.com] > Sent: Wednesday, May 12, 2010 3:10 AM > To: Thom Brown > Cc: pgsql-general@postgresql.org > Subject: 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 > > > And, the reason it works is that (1, NULL) IS NOT equal to (1, NULL), because NULL is never equal to NULL, (result of comparing NULL to NULL is NULL, and not TRUE or FALSE). So these 2 rows: 1, NULL 1, NULL do not violate "uniqueness" of your constraint. Regards, Igor Neyman