在引用表中没有与外键中的引用列列表匹配的主键或候选键 [英] There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key

问题描述

在SQL Server中，出现此错误->

In SQL Server , I got this error ->

引用的表中没有主键或候选键 与外键中的引用列列表匹配的"BookTitle" 'FK_ BookCopy _Title__2F10007B'."

"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'."

我首先创建了一个称为BookTitle关系的关系.

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))

然后，我创建了一个称为BookCopy关系的关系.此关系需要引用BookTitle关系的主键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))

但是我无法创建BookCopy关系，因为出现了上述错误.

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

我真的很感谢一些有用的帮助.

I really appreciate some useful help.

推荐答案

外键通过将列连接到另一个表中的唯一键来工作，并且该唯一键必须定义为某种形式的唯一索引，因为它是主索引键或其他唯一索引.

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.

目前，您唯一的唯一索引是ISBN, Title上的复合索引，这是您的主键.

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

有很多选项可供您选择，具体取决于BookTitle拥有什么以及其中的数据关系.

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

我会猜测，书名中的每一行ISBN都是唯一的.假设是这种情况，然后将主键更改为仅在ISBN上，并更改BookCopy，以便您拥有ISBN并加入ISBN，而不是Title.

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.

如果需要将主键保持为ISBN, Title，则需要将ISBN存储在BookCopy中，并将Title和外键存储在两列中，或者需要在BookTitle(Title)上创建唯一索引)作为不同的索引.

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.

通常，您需要确保REFERENCES子句中的一个或多个列与父表中的唯一索引完全匹配:在您的情况下，它会失败，因为您在Title一个人.

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.

这篇关于在引用表中没有与外键中的引用列列表匹配的主键或候选键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！