CODEWITHSUNDEEP
sqlsql-serverforeign-keysprimary-keycomposite-primary-key
aredlich
aredlich

Reputation: 1

Trouble Referencing Two-part Primary Key in SQL Server

I created this table with a two-part primary key:

Create Table Part
    (PartNumber         Int             Not Null,
    VendorNumber        Int             Not Null References Vendor(VendorNumber),
    PartDescription     VarChar(100)    Not Null,
    UnitPrice           Money           Not Null,
    MTDSales            Money           Not Null,
    YTDSales            Money           Not Null,
    UnitsOnHand         Int             Not Null,
    UnitsAllocated      Int             Not Null,
    ReorderPoint        Int             Not Null,
    VendorPrice         Money           Not Null,
    MinimumOrderQuantity Int            Not Null,
    ExpectedLeadTime    Datetime        Not Null,
    Primary Key (PartNumber, VendorNumber))

And another table is referencing the Part table's primary keys:

Create Table OrderDetail
(OrderNumber        Int             Not Null References Orders(OrderNumber),
SEQNumber           Int             Not Null,
PartNumber          Int             Not Null References Part(PartNumber),
VendorNumber        Int             Not Null References Part(VendorNumber),
NumberOrdered       Int             Not Null,
QuotedPrice         Money           Not Null,
LineTotal           Int             Not Null,
Comments            VarChar(100)    Not Null,
Primary Key (OrderNumber, SEQNumber))

When running the program, the following error is returned:

Msg 1776, Level 16, State 0, Line 99 There are no primary or candidate keys in the referenced table 'Part' that match the referencing column list in the foreign key 'FK__OrderDeta__PartN__239E4DCF'.

Could anyone provide suggestions on how to resolve the missing primary key error?

Upvotes: 0

Views: 68

Answers (1)

Jakub Lortz
Jakub Lortz

Reputation: 14896

You need to create one composite foreign key, not two single-column keys. You can do it as a separate constraint in create table:

Create Table OrderDetail
(
    OrderNumber         Int             Not Null References Orders(OrderNumber),
    SEQNumber           Int             Not Null,
    PartNumber          Int             Not Null,
    VendorNumber        Int             Not Null,
    NumberOrdered       Int             Not Null,
    QuotedPrice         Money           Not Null,
    LineTotal           Int             Not Null,
    Comments            VarChar(100)    Not Null,
    Primary Key (OrderNumber, SEQNumber),
    constraint FK_OrderDetail_Part foreign key (PartNumber,VendorNumber) 
                                   references Part (PartNumber,VendorNumber)
)

Upvotes: 4

Related Questions