Postgres Pro
Downloads
Home   >   mailing lists

Thread: Constrain duplicate patterns

Constrain duplicate patterns

From
Russell Shaw
Date:
Hi,

Is there a way to make postgresql report an error if when
inserting data, a multiple-column combination is duplicated?

CREATE TABLE parts_vendors (
     part_id integer references parts (part_id),
     vendor_id integer references vendors (vendor_id),
     ...
)

part_id  vendor_id
------------------
1        3
2        5 <--+
7        9    |
2        5 <--+-- duplicate pattern
...

Re: Constrain duplicate patterns

From
Russell Shaw
Date:
Russell Shaw wrote:
> Hi,
>
> Is there a way to make postgresql report an error if when
> inserting data, a multiple-column combination is duplicated?
>
> CREATE TABLE parts_vendors (
>     part_id integer references parts (part_id),
>     vendor_id integer references vendors (vendor_id),
>     ...
> )
>
> part_id  vendor_id
> ------------------
> 1        3
> 2        5 <--+
> 7        9    |
> 2        5 <--+-- duplicate pattern
> ...
>

Oops, i can just do a select on the various columns
to detect this...

Re: Constrain duplicate patterns

From
Michael Glaesemann
Date:
On Feb 1, 2004, at 6:06 PM, Russell Shaw wrote:

> Hi,
>
> Is there a way to make postgresql report an error if when
> inserting data, a multiple-column combination is duplicated?

I believe you're looking for UNIQUE(part_id, vendor_id), if you want to
prevent this from happening. If you just want it to report an error,
but allow the insert/update anyway, maybe a trigger? Not sure about
that though.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

Re: Constrain duplicate patterns

From
joseph speigle
Date:
I found a way to do it like this

create table parts (part_id int, description character varying(33) constraint part_id unique , primary key (part_id));
create table vendors (vendor_id int, description character varying(33) constraint vendor_id unique, primary key
(vendor_id)); 
CREATE TABLE parts_vendors ( part_id integer unique references parts (part_id), vendor_id integer unique references
vendors(vendor_id)); 

On Sun, Feb 01, 2004 at 08:11:50PM +1100, Russell Shaw wrote:
> Russell Shaw wrote:
> >Hi,
> >
> >Is there a way to make postgresql report an error if when
> >inserting data, a multiple-column combination is duplicated?
> >
> >CREATE TABLE parts_vendors (
> >    part_id integer references parts (part_id),
> >    vendor_id integer references vendors (vendor_id),
> >    ...
> >)
> >
> >part_id  vendor_id
> >------------------
> >1        3
> >2        5 <--+
> >7        9    |
> >2        5 <--+-- duplicate pattern
> >...
> >
>
> Oops, i can just do a select on the various columns
> to detect this...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

--
joe speigle

Re: Constrain duplicate patterns

From
Russell Shaw
Date:
Michael Glaesemann wrote:
>
> On Feb 1, 2004, at 6:06 PM, Russell Shaw wrote:
>
>> Hi,
>>
>> Is there a way to make postgresql report an error if when
>> inserting data, a multiple-column combination is duplicated?
>
> I believe you're looking for UNIQUE(part_id, vendor_id), if you want to
> prevent this from happening. If you just want it to report an error, but
> allow the insert/update anyway, maybe a trigger? Not sure about that
> though.

Hi,
Thanks. I used this:

create table parts_vendors (
     part_id integer references parts (part_id),
     vendor_id integer references vendors (vendor_id),
     unique(part_id,vendor_id)
)