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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Russell Shaw wrote:
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 (AT) postgresql (DOT) org)

On Feb 1, 2004, at 6:06 PM, Russell Shaw wrote:

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

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:
--
joe speigle

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Michael Glaesemann wrote:
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)
)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly