[sql] There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

In SQL Server , I got this error ->

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

I first created a relation called the BookTitle relation.

CREATE TABLE BookTitle (
ISBN            CHAR(17)       NOT NULL,
Title           VARCHAR(100)   NOT NULL,
Author_Name     VARCHAR(30)    NOT NULL,
Publisher       VARCHAR(30)    NOT NULL,
Genre           VARCHAR(20)    NOT NULL,
Language        CHAR(3)        NOT NULL,    
PRIMARY KEY (ISBN, Title))

Then I created a relation called the BookCopy relation. This relation needs to reference to the BookTitle relation's primary key, Title.

CREATE TABLE BookCopy (
CopyNumber         CHAR(10)            NOT NULL,
Title              VARCHAR(100)        NOT NULL,
Date_Purchased     DATE                NOT NULL,
Amount             DECIMAL(5, 2)       NOT NULL,
PRIMARY KEY (CopyNumber),
FOREIGN KEY (Title) REFERENCES BookTitle(Title))

But I can't create the BookCopy relation because the error stated above appeared.

I really appreciate some useful help.

You need either

• A unique index on Title in BookTitle
• An ISBN column in BookCopy and the FK is on both columns

A foreign key needs to uniquely identify the parent row: you currently have no way to do that because Title is not unique.

Another thing is - if your keys are very complicated sometimes you need to replace the places of the fields and it helps :

if this dosent work:

foreign key (ISBN, Title) references BookTitle (ISBN, Title)

Then this might work (not for this specific example but in general) :

foreign key (Title,ISBN) references BookTitle (Title,ISBN)

BookTitle have a Composite key. so if the key of BookTitle is referenced as a foreign key you have to bring the complete composite key.

So to resolve the problem you need to add the complete composite key in the BookCopy. So add ISBN column as well. and they at the end.

foreign key (ISBN, Title) references BookTitle (ISBN, Title)