Thread: multiple primary keys and reference

I have a table, OrderDetail, with two primary keys OrderID and ProductID

I want another table, Claims, to reference the primary keys of 
OrderDetail so that the pair has to match between table not just one 
attrib. I can't figure out how to do this from the docs I have. can 
anyone help? thankyou in advance

matthew berardi
weil publishing

CREATE TABLE Parts(
PartNo varchar(20) NOT NULL,
machtypesid int4 NOT NULL,
BestPrice decimal(100,2),
Comment text,
PRIMARY KEY (PartNo,machtypesid));

CREATE TABLE MachDefs(
defid serial NOT NULL UNIQUE PRIMARY KEY,
Description text,
PartNo varchar(20),
MachTypeID int4 NOT NULL,
FOREIGN KEY (PartNo,MachTypeID) REFERENCES Parts (PartNo,machtypesid));

-- 
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel:    +30-10-8981112
fax:    +30-10-8981877
email:  achill@matrix.gatewaynet.com       mantzios@softlab.ece.ntua.gr

On Fri, 7 Jun 2002, Matt wrote:

> I have a table, OrderDetail, with two primary keys OrderID and ProductID

I assume you mean it has a primary key with two columns.
>
> I want another table, Claims, to reference the primary keys of
> OrderDetail so that the pair has to match between table not just one
> attrib. I can't figure out how to do this from the docs I have. can
> anyone help? thankyou in advance

If I understand what you want, use the table constraint form of foreign
keys:

create table a (a int, b int, primary key(a,b));
create table b (a int, b int, foreign key(a,b) references a);