Thread: Weird unique constraint

Weird unique constraint

From
Mike Christensen
Date:
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

Re: Weird unique constraint

From
Thom Brown
Date:
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  

Re: Weird unique constraint

From
Thom Brown
Date:
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 

Re: Weird unique constraint

From
"A. Kretschmer"
Date:
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

Re: Weird unique constraint

From
Mike Christensen
Date:
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
>

Re: Weird unique constraint

From
Thom Brown
Date:
On 12 May 2010 08:09, Mike Christensen <mike@kitchenpc.com> wrote:
By golly you're right; maybe I should try this stuff before I email
hundreds of people :)



Nah, gives us something to read :)

Thom

Re: Weird unique constraint

From
"Rob Richardson"
Date:
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

Re: Weird unique constraint

From
Grzegorz Jaśkiewicz
Date:
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.

Re: Weird unique constraint

From
"Igor Neyman"
Date:

> -----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