SQL Server 2008：表中的列与现有主键或唯一约束不匹配

SQL Server 2008: The columns in table do not match an existing primary key or unique constraint

提问by 109221793

I need to make some changes to a SQL Server 2008 database.

我需要对 SQL Server 2008 数据库进行一些更改。

This requires the creation of a new table, and inserting a foreign key in the new table that references the Primary key of an already existing table. So I want to set up a relationship between my new tblTwo, which references the primary key of tblOne.

这需要创建一个新表，并在新表中插入一个引用现有表主键的外键。所以我想在我的新tblTwo之间建立一个关系，它引用了tblOne的主键。

However when I tried to do this (through SQL Server Management Studio) I got the following error:

但是，当我尝试执行此操作（通过 SQL Server Management Studio）时，出现以下错误：

The columns in table 'tblOne' do not match an existing primary key or UNIQUE constraint

表 'tblOne' 中的列与现有主键或 UNIQUE 约束不匹配

I'm not really sure what this means, and I was wondering if there was any way around it?

我不太确定这意味着什么，我想知道是否有任何解决方法？

回答by Damien_The_Unbeliever

It means that the primary key in tblOne hasn't been properly declared - you need to go to tblOne and add the PRIMARY KEY constraint back onto it.

这意味着 tblOne 中的主键尚未正确声明 - 您需要转到 tblOne 并将 PRIMARY KEY 约束添加回它。

If you're sure that tblOne does have a PRIMARY KEY constraint, then maybe there are multiple tblOne tables in your DB, belonging to different schemas, and your references clause in your FK constraint is picking the wrong one.

如果您确定 tblOne 确实有 PRIMARY KEY 约束，那么您的数据库中可能有多个 tblOne 表，属于不同的架构，并且您的 FK 约束中的引用子句选择了错误的一个。

If there's a composite key (which your comment would indicate), then you have to include both columns in your foreign key reference also. Note that a table can't have multiple primary keys - but if it has a composite key, you'll see a key symbol next to each column that is part of the primary key.

如果有一个复合键（您的评论会指出），那么您还必须在外键引用中包含这两列。请注意，一个表不能有多个主键 - 但如果它有一个复合键，您会在作为主键一部分的每一列旁边看到一个键符号。

回答by alanh

If you have a composite key the order is important when creating a FK, and sometimes the order is not how it is displayed.

如果您有复合键，则在创建 FK 时顺序很重要，有时顺序不是它显示的方式。

What I do is go to the Keys section of the table1 and select script primary key as create to clipboard and then create FK using the order as shown in script

我要做的是转到 table1 的 Keys 部分并选择脚本主键作为创建到剪贴板，然后使用脚本中所示的顺序创建 FK

回答by Ruan

If you still get that error after you have followed all advice from the above answers and everything looks right.

如果您在遵循上述答案中的所有建议后仍然遇到该错误并且一切正常。

One way to fix it is by Removing your Primary keys for both tables, Save, Refresh, and add them again. Then try to add your relationship again.

修复它的一种方法是删除两个表的主键，保存，刷新，然后再次添加它们。然后尝试再次添加您的关系。

回答by 100r

I've had this situation that led me to this topic. Same error but another cause. Maybe it will help someone.

我遇到过这种情况，这让我想到了这个话题。同样的错误，但另一个原因。也许它会帮助某人。

Table1
ColA (PK)
ColB (PK)
ColC


Table2
ID (PK)
ColA
COLB

When trying to create foreign key in Table2 I've choose values from combobox in reverse order

尝试在 Table2 中创建外键时，我以相反的顺序从组合框中选择值

Table1.ColB = Table2.ColB
Table1.ColA = Table2.ColA

This was throwing me an error like in topic name. Creating FK keeping order of columns in Primary key table as they are, made error disappear.

这给我抛出了一个类似主题名称的错误。创建 FK 保持主键表中列的顺序，使错误消失。

Stupid, but.. :)

愚蠢，但是.. :)

回答by Basheer AL-MOMANI

This Error happened with me WhenI tried to add foreign keyconstraint starting from PrimaryKey Table

这个错误发生在我身上，When我试图add foreign key从PrimaryKey Table

Simpy go to other table and and create this foreign keyconstraint fromthere (foreign key Table)

Simpy 转到其他表并在那里进行create this foreign key约束from(foreign key Table)

回答by Richard Pursehouse

This issue caught me out, I was adding the relationship on the wrong table. So if you're trying to add a relationship in table A to table B, try adding the relationship in table B to table A.

这个问题引起了我的注意，我在错误的表上添加了关系。因此，如果您尝试将表 A 中的关系添加到表 B，请尝试将表 B 中的关系添加到表 A。

回答by Caimen

I've found another way to get this error. This can also happen if you are trying to make a recursive foreign key (a foreign key to the primary key in the same table) in design view in SQL Management Studio. If you haven't yet saved the table with the primary key it will return this message. Simply save the table then it will allow you to create the foreign key.

我找到了另一种方法来获得这个错误。如果您尝试在 SQL Management Studio 的设计视图中创建递归外键（同一表中主键的外键），也会发生这种情况。如果您尚未使用主键保存表，它将返回此消息。只需保存表，然后它就可以让您创建外键。

回答by Pete Kozak

If you have data in your tables this could be the issue.

如果您的表中有数据，这可能是问题所在。

In my case I had some data in the Account table that I loaded at 3 pm, and some data in Contact table that I loaded at 3:10 pm, so Contact table had some values that weren't in my Account table yet.

就我而言，我在下午 3 点加载的 Account 表中有一些数据，在下午 3:10 加载的 Contact 表中有一些数据，因此 Contact 表中有一些值还没有出现在我的 Account 表中。

I ended up deleting these values from the contact table and then managed to add a key without any problems.

我最终从联系表中删除了这些值，然后设法添加了一个键，没有任何问题。

回答by Samsky

If nothing helps, then this could be the reason: Considering this case: Table A: Column 1 (Primary Key) Column 2 (Primary Key) Column 3 Column 4

如果没有任何帮助，那么这可能是原因：考虑这种情况：表 A：第 1 列（主键）第 2 列（主键）第 3 列第 4 列

Table B: Column a (Primary Key) Column b Column c

表 B：a 列（主键） b 列 c 列

when you are defining a dependency B to A, then you are forced to respect the order in which the primaries are defined.

当您定义 B 到 A 的依赖项时，您必须遵守定义主项的顺序。

That's mean your dependency should look like this: Table A Table B Column 1 Column b Column 2 Column c

这意味着您的依赖项应如下所示：表 A 表 B 列 1 列 b 列 2 列 c

AND NOT: Table A Table B Column 2 Column c Column 1 Column b

AND NOT：表A 表B 第2 列c 列第1 列b

then this will lead to the error you are encountering.

那么这将导致您遇到的错误。

回答by Guillem Vicens

That looks like you are trying to create a foreign key in tblTwo that does not match (or participate) with any primary key or unique index in tblOne.

看起来您正在尝试在 tblTwo 中创建一个与 tblOne 中的任何主键或唯一索引都不匹配（或参与）的外键。

Check this link on MSDNregarding it. Here you have another link with a practical case.

在MSDN上查看有关它的链接。在这里，您还有一个包含实际案例的链接。

EDIT:

编辑：

Answwering to your comment, I understand you mean there are 2 fields in the primary key (which makes it a composite). In SQL it is not possible to have 2 primary keys on the same table.

回答您的评论，我理解您的意思是主键中有 2 个字段（这使它成为一个组合）。在 SQL 中，同一个表上不可能有 2 个主键。

IMHO, a foreign key field should always refer to a single register in the referenced table (i.e. the whole primary key in your case). That means you need to put both fields of the tblOne primary key in tblTwo before creating the foreign key.

恕我直言，外键字段应始终引用引用表中的单个寄存器（即您的情况下的整个主键）。这意味着您需要在创建外键之前将 tblOne 主键的两个字段都放在 tblTwo 中。

Anyway, I have investigated a bit over the Internet and it seems SQL Server 2008 (as some prior versions and other RDBMS) gives you the possibility to reference only part of the primary key as long as this part is a candidate key (Not Null and Unique) and you create an unique constraint on it.

无论如何，我已经在 Internet 上进行了一些调查，似乎 SQL Server 2008（作为某些先前版本和其他 RDBMS）让您可以仅引用主键的一部分，只要这部分是候选键（非空和唯一），然后在其上创建唯一约束。

I am not sure you can use that in your case, but check this linkfor more information on it.

我不确定您是否可以在您的情况下使用它，但请查看此链接以获取更多信息。