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

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to key

Examples related to candidate