There are no primary or candidate keys in the referenced table 'Reviewers' that match the referencing column

Question

I'm getting this error:

There are no primary or candidate keys in the referenced table 'Reviewers' that match the referencing column list in the foreign key 'FK_Review_Review_ID'.
Msg 1750, Level 16, State 0, Line 57
Could not create constraint or index. See previous errors.

My code:

/* Table Creation Products */

CREATE TABLE Products
(
    Product_Name VARCHAR(120) NOT NULL
    ,Product_URL VARCHAR(165) NOT NULL
    ,Price MONEY CHECK (Price > 0) NOT NULL
)
GO

/* Entity Integrity by Primary Key*/
ALTER TABLE Products
ADD CONSTRAINT PK_Products_Product_URL
PRIMARY KEY (Product_URL)
GO

/* Table Creation Reviewers */
CREATE TABLE Reviewers
(
    Reviewer_ID INT NOT NULL
    ,Review_ID INT NOT NULL
    ,Review_Summary VARCHAR(15) NOT NULL
    ,Review VARCHAR(280) NOT NULL
    ,Review_Date DATE
    ,Overall_Rating INT NOT NULL
    ,Helpful INT
    ,Unhelpful INT
)
GO

ALTER TABLE Reviewers
ADD CONSTRAINT PK_Reviewers_Reviewer_ID_Review_ID
PRIMARY KEY (Reviewer_ID, Review_ID)
GO

/* Table Creation Review */
CREATE TABLE Review
(
    Product_URL VARCHAR(165) NOT NULL
    ,Review_ID INT NOT NULL
)
GO

/* Entity Integrity by Primary Key*/ 
ALTER TABLE Review
ADD CONSTRAINT PK_Product_URL_Review_ID
PRIMARY KEY (Product_URL, Review_ID)
GO

/* Entity Integrity by Foreign Key */
ALTER TABLE Review
ADD CONSTRAINT FK_Review_Product_URL
FOREIGN KEY (Product_URL)
REFERENCES Products (Product_URL)
GO

ALTER TABLE Review
ADD CONSTRAINT FK_Review_Review_ID
FOREIGN KEY (Review_ID)
REFERENCES Reviewers (Review_ID)
GO

score 1 · Answer 1 · answered May 10 '18 at 08:11

Since your Reviewer table has a primary key on (Reviewer_ID, Review_ID), any child table that wants to reference Reviewer must also provide the same two columns to establish a FK relationship.

You CANNOT reference only part of a primary key - it's all or nothing: either you provide ALL the columns of the primary key of your referenced table - or you cannot establish an FK relationship.

So in your case, you would have add a Reviewer_ID column to your Review table, so that you can use the two required columns for the FK relationship:

ALTER TABLE dbo.Review
ADD CONSTRAINT FK_Review_Review_ID
    FOREIGN KEY (Review_ID, Reviewer_ID) 
    REFERENCES dbo.Reviewers (Review_ID, Reviewer_ID)

There are no primary or candidate keys in the referenced table 'Reviewers' that match the referencing column

1 Answers1