CODEWITHSUNDEEP
sqlsql-serverforeign-keysprimary-keycomposite-key
Dragon Warrior
Dragon Warrior

Reputation: 327

Not able to create foreign key "There are no primary or candidate keys in the referenced table"

This is my table 1:

CREATE TABLE PurchasedProducts
(
   Purchase_Order_No int,
   Purchase_Product_ID int FOREIGN KEY REFERENCES Inventory(In_Product_ID),
   Purchase_Quantity int NOT NULL,
   Purchase_Status varchar(7) NOT NULL,

   PRIMARY KEY(Purchase_Order_No, Purchase_Product_ID)
);

This my table 2:

CREATE TABLE PurchasedDate
(
    PD_PO_No int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES PurchasedProducts(Purchase_Order_No),
    PD_Date date NOT NULL
);

I executed the first table successfully, but when I execute the second table It is showing this error message:

There are no primary or candidate keys in the referenced table 'PurchasedProducts' that match the referencing column list in the foreign key 'FK__Purchased__PD_PO__0B5CAFEA'.

I don't what is the problem is. Please help me!

Upvotes: 0

Views: 2340

Answers (1)

marc_s
marc_s

Reputation: 754338

The primary key in your PurchasedProducts table is made up of two columns:

PRIMARY KEY(Purchase_Order_No, Purchase_Product_ID)

So any child table that wants to reference that also must have these exact two columns:

CREATE TABLE PurchasedDate
(
    PD_PO_No int NOT NULL PRIMARY KEY,
    Purchase_Product_ID INT NOT NULL,
    PD_Date date NOT NULL
);

ALTER TABLE dbo.PurchasedDate
ADD CONSTRAINT FK_PurchaseDate_PurchasedProducts 
    FOREIGN KEY(PD_PO_No, Purchase_Product_ID)
    REFERENCES PurchasedProducts(Purchase_Order_No, Purchase_Product_ID)

A foreign key can only reference the whole primary key of a parent table - you cannot reference only one column out of 2 from the parent table's PK.

Upvotes: 2

Related Questions