Weird unique constraint

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

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