foreign key and primary key error

Question

hi expert

as per our last discussion regarding duplicate invoice no. and you have suggetsed code like:

create table Invoice
(
    companyID int,
    invno varchar(50),
    primary key (companyID,Invno)
)

worked fine.

but another table is invcarat same has field like:

companyID(int)          invno(varchar)
------------------------------------------------------

now i have to create foreign key on invcarat(invno) but error has occured while creating of foreign key that "primary key on reference table invoice doesn't exist". while i have created primary key on invoice as above.

kindly reply how to generate relationship bewteen two tables.

thanks in advance.

Answer 1

The foreign key should be on invcarat (companyID, invno) and should reference invoice (companyID, invno). The invno columns itself is not a primary key, so you can't use it as a foreign key target. You could create a unique key on it, but they you might as well just change the primary key.