SQL Server 2008：表中的列与现有主键或唯一约束不匹配
我需要对 SQL Server 2008 数据库进行一些更改。
这需要创建一个新表，并在新表中插入一个引用现有表主键的外键。所以我想在我的新 tblTwo 之间建立关系，它引用 tblOne 的主键。
但是，当我尝试执行此操作（通过 SQL Server Management Studio）时，出现以下错误：
表“tblOne”中的列不 匹配现有主键或 唯一约束
我不太确定这意味着什么，我想知道是否有什么办法可以解决它？
I need to make some changes to a SQL Server 2008 database.
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.
However when I tried to do this (through SQL Server Management Studio) I got the following error:
The columns in table 'tblOne' do not
match an existing primary key or
UNIQUE constraint
I'm not really sure what this means, and I was wondering if there was any way around it?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（14）
这意味着 tblOne 中的主键尚未正确声明 - 您需要转到 tblOne 并将 PRIMARY KEY 约束添加回其上。
如果您确定 tblOne 确实有 PRIMARY KEY 约束，那么您的数据库中可能有多个 tblOne 表，属于不同的模式，并且 FK 约束中的引用子句选择了错误的表。
如果存在复合键（您的注释会指出），那么您还必须在外键引用中包含这两列。请注意，一个表不能有多个主键 - 但如果它有一个复合键，您将在作为主键一部分的每列旁边看到一个键符号。
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.
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.
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.
如果您有复合键，则在创建 FK 时顺序很重要，有时顺序并不是它的显示方式。
我所做的是转到 table1 的“Keys”部分，选择脚本主键作为“创建到剪贴板”，然后使用脚本中所示的顺序创建 FK
If you have a composite key the order is important when creating a FK, and sometimes the order is not how it is displayed.
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
我曾经遇到过这样的情况，这让我想到了这个话题。同样的错误，但另一个原因。也许它会对某人有所帮助。
当尝试在 Table2 中创建外键时，我以相反的顺序从组合框中选择值
，这向我抛出了一个错误，如主题名称中的错误。创建 FK 保持主键表中列的顺序不变，使错误消失。
愚蠢，但是..:)
I've had this situation that led me to this topic. Same error but another cause. Maybe it will help someone.
When trying to create foreign key in Table2 I've choose values from combobox in reverse order
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.
Stupid, but.. :)
如果您在遵循上述答案的所有建议并且一切看起来都正确后仍然收到该错误。
解决此问题的一种方法是删除两个表的主键，保存，刷新，然后再次添加它们。
然后尝试再次添加您的关系。
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.
这个错误发生在我身上
当我试图从PrimaryKey Table开始添加外键约束，只需转到其他表并
创建这个外键约束来自那里（外键表）This Error happened with me
WhenI tried toadd foreign keyconstraint starting fromPrimaryKey TableSimpy go to other table and and
create this foreign keyconstraintfromthere(foreign key Table)这个问题让我很困惑，我在错误的表上添加了关系。因此，如果您尝试将表 A 中的关系添加到表 B，请尝试将表 B 中的关系添加到表 A。
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.
看起来您正在尝试在 tblTwo 中创建一个与 tblOne 中的任何主键或唯一索引不匹配（或参与）的外键。
请查看 MSDN 上的此链接。这里有另一个链接，其中包含实际案例。
编辑：
回答您的评论，我理解您的意思是主键中有 2 个字段（这使其成为组合）。在 SQL 中，同一个表上不可能有 2 个主键。
恕我直言，外键字段应始终引用引用表中的单个寄存器（即您的情况下的整个主键）。这意味着您需要在创建外键之前将 tblOne 主键的两个字段都放入 tblTwo 中。
无论如何，我在互联网上进行了一些调查，似乎 SQL Server 2008（以及一些早期版本和其他 RDBMS）使您可以仅引用主键的一部分，只要这部分是候选键（非空和唯一），并且您对其创建唯一约束。
我不确定您是否可以在您的情况下使用它，但请检查此 链接 了解更多信息。
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.
Check this link on MSDN regarding it. Here you have another link with a practical case.
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.
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.
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.
I am not sure you can use that in your case, but check this link for more information on it.
我发现列名必须匹配。
例子：
因此，如果 tblOne 的 id 称为categoryId，则 tblTwo 中的引用也必须称为categoryId。
当我尝试在两个表之间创建外键时，我注意到了这一点，这两个表都将列名“id”作为主键。
I have found that the column names must match.
Example:
So if tblOne has id called categoryId a reference in tblTwo must also be called categoryId.
I noticed this when trying to create foreign key between 2 tables that both had the column name "id" as primary key.
如果没有任何帮助，那么原因可能是：
考虑到这种情况：
表A：
第 1 列（主键）
第 2 列（主键）
第3栏
表 B第 4 栏
：
a 列（主键）
b栏
当您定义 B 到 A 的依赖关系时，您必须遵守c 列
定义主项的顺序。
这意味着您的依赖关系应该如下所示：
表A 表B
第 1 列 b 列
第 2 栏 c 栏
且非：
表A 表B
第 2 列 c 列
Column 1 Column b
那么这将导致您遇到的错误。
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
Table B:
Column a (Primary Key)
Column b
Column c
when you are defining a dependency B to A, then you are forced to respect the order in which the primaries are defined.
That's mean your dependency should look like this:
Table A Table B
Column 1 Column b
Column 2 Column c
AND NOT:
Table A Table B
Column 2 Column c
Column 1 Column b
then this will lead to the error you are encountering.
我找到了另一种方法来解决这个错误。如果您尝试在 SQL Management Studio 的设计视图中创建递归外键（同一表中主键的外键），也可能会发生这种情况。如果您尚未使用主键保存表，它将返回此消息。只需保存表，然后您就可以创建外键。
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.
如果您的表中有数据，这可能就是问题所在。
就我而言，我在下午 3 点加载的“帐户”表中有一些数据，在下午 3:10 加载的“联系人”表中有一些数据，因此“联系人”表中有一些我的“帐户”表中还没有的值。
我最终从联系人表中删除了这些值，然后成功添加了一个键，没有任何问题。
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.
I ended up deleting these values from the contact table and then managed to add a key without any problems.
就我而言，发生错误是因为我使用 SSMS 设计器窗格添加关系（指向复合唯一约束）。
手动编写 ALTER TABLE 查询，并尊重唯一约束的组合键的顺序，我能够定义外键约束。
In my case, the error occurred because I used the SSMS designer pane to add the relationship (which was pointing towards a composite unique constraint).
Writing the ALTER TABLE query by hand, and respecting the order of the composite keys of the unique constraint, I was able to define the foreign key constraint.
在我的自引用表中，我混淆了主键和外键字段。
In my self-referencing table I mixed up the primary key and foreign key fields.
另请注意，在使用另一个表列设置外键时定义主键的表内没有现有数据。
这就是我的案例中错误的原因。
我必须将备份清空表设置关系，然后将数据上传回来。
分享我的经验
正在使用 ms sql smss
Kindly also see that there are no existing data inside the table where the primary key is defined while setting the foreign key with another table column.
this was the cause of the error in my case.
I had to take backup empty the table set the relationship and then upload the data back.
sharing my experience
Was using ms sql smss