61

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.

philipxy
  • 14,867
  • 6
  • 39
  • 83
user2622438
  • 665
  • 1
  • 6
  • 9

5 Answers5

84

Foreign keys work by joining a column to a unique key in another table, and that unique key must be defined as some form of unique index, be it the primary key, or some other unique index.

At the moment, the only unique index you have is a compound one on ISBN, Title which is your primary key.

There are a number of options open to you, depending on exactly what BookTitle holds and the relationship of the data within it.

I would hazard a guess that the ISBN is unique for each row in BookTitle. ON the assumption this is the case, then change your primary key to be only on ISBN, and change BookCopy so that instead of Title you have ISBN and join on that.

If you need to keep your primary key as ISBN, Title then you either need to store the ISBN in BookCopy as well as the Title, and foreign key on both columns, OR you need to create a unique index on BookTitle(Title) as a distinct index.

More generally, you need to make sure that the column or columns you have in your REFERENCES clause match exactly a unique index in the parent table: in your case it fails because you do not have a single unique index on Title alone.

Chris J
  • 30,688
  • 6
  • 69
  • 111
  • The reason for this is because when you have a foreign key it shouldn't have to scan the target table to make the relation right? – HumbleWebDev Aug 15 '16 at 13:15
  • 4
    No, it's because foreign keys conceptually always map to a single row in the parent table. This is enforced by a unique constraint on the table which on many RDBMSs is implemented as an index. But it doesn't have to be. – Chris J Aug 17 '16 at 10:59
  • Right, I think that's both a better and simpler explanation. – HumbleWebDev Aug 17 '16 at 15:59
23

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

if this doesn't 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)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Dan
  • 231
  • 2
  • 2
  • 5
    Thanks! I finally got past that stupid error. [How important is the order of columns in indexes?](https://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes) explains why the order matters. – Darsstar Feb 05 '15 at 14:10
7

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
5

BookTitle has 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)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Talha Ahmed Khan
  • 15,043
  • 10
  • 42
  • 49
0

Faced the same issue now. Simple explanation: your second table does not have a primary key. The foreign key of your first table must be a primary key in another table you are trying link.

Mr.B
  • 3,484
  • 2
  • 26
  • 40