foreign key in sql using multiple primary key references?

Question

create table product2
(mfr_id int not null,
product_id int not null,
description varchar(20),
price money,
qty_on_hand integer,
constraint product2_pk primary key(mfr_id,product_id))

create table orders2
(order_num int not null,
order_date datetime not null,
cust int,
rep int,
mfr int,
product int not null,
qty int,
amount money,
constraint orders2_pk primary key(order_num))

alter table orders2
add constraint orders2_fk5
foreign key(product)
references product2(mfr_id,product_id)

error:Number of referencing columns in foreign key differs from number of referenced columns, table 'orders2'.

if i use only product_id as reference then i get error:
There are no primary or candidate keys in the referenced table 'product2' that match the referencing column list in the foreign key 'orders2_fk5'.

Answer 1

You can't use the multi column primary key in the way that you want.



You can do a couple of things, 1. create a new table that uses an identity as a Primary key along with mfr_id and product_id.



or what I think you should do is add a surrogate - like key into the product table and remove the primary key constraint from mfr_id and product_id. Then you would have a single column PK to go with your order table.



One question, if the PK for products is both manufacture and product id, that would mean there must be duplicate product id's. Then you can't use product_id as the foreign key in orders.

Answer 2

In a database table, the primary key is a unique identifier for each element in that specific table. A table may also have a foreign key, which matches to a primary key in another table.

Answer 3

La desilusión es prejudicial por este razón no desilusiones ya que la solución para quedarte embarazada hay, es aquí https://tr.im/YqsO7 Tengo una amiga que se quedó embarazada este método, ulteriormente a muchas tentativas y fiascos, descubrí este sitio y intenté la fórmula, al fin y al cabo nada podía perder sino que ha obtenido todo ya que se quedó embarazada en solo 2 semanas.

Answer 4

What DBMS are you using? While some can use multicolumn foreign keys (Oracle, for one), not all can.