在 SQL Server 2008 中创建复合外键 [英] Creating a composite foreign key in SQL Server 2008

问题描述

我有两个表要为其创建外键.

I have two tables that I'd like to create a foreign key for.

主表

PK - Key1 - varchar(20)
PK - Key2 - date

次表

PK - AutoID
FK - Key1 - varchar(20)
FK - Key2 - date

当我尝试创建主表和辅助表之间的关系时，我不断收到消息

When I try to create the relationship between the primary and secondary table, I keep getting the message

主表中的列与主键或唯一键不匹配约束.

The columns in the Primary Table do not match a primary key or unique constraint.

辅助表中可能有许多记录具有相同的 Key1 和 Key2，因此我们将主键设为自动创建的编号.

There can be many records in the secondary table with the same Key1 and Key2 so we made the primary key an auto created number.

关于如何设置这两个表之间的外键关系有什么想法吗?

Any thoughts on how I can set up the foreign key relationship between these two tables?

推荐答案

其中一些是重点，其中一些是其他人遇到此类问题的上下文(就像任何人实际上首先搜索?)

Some of this is focused, some of this is context for others having any sort of problem like this (like anyone actually searches first?)

当您在创建密钥时遇到问题时，首先要检查的是确保两个表中的数据类型没有不匹配.如果你有一个 bigint 和另一个 int ，它会爆炸.这适用于所有键，但如果您使用多个字段，则更有可能突然出现.简单的数学计算说明了机会增加的原因.

The first thing to check when you have a problem creating a key is make sure you did not mismatch the data types in the two tables. If you have an bigint in one and an int in the other, it will blow. This is true on all keys, but more likely to crop up if you use multiple fields. Simple math shows the reason why the chance increases.

下一个问题是数据.如果由于数据原因无法创建key，则必须找出子表中存在哪些父表中不存在的内容.LEFT JOIN 表(连接的第二个/左侧的辅助表)并且只包括主表为空的行.您要么必须在父表中创建这些记录，要么删除它们.

The next issue is data. If you cannot create the key due to data, you have to find out what exists in the child table that does not exist in the parent table. LEFT JOIN the tables (secondary on the second/left side of the join) and only include rows where the primary table is null. You will either have to create these records in the parent table or get rid of them.

解决"这个问题的一种方法是在父表上设置一个新的主键.然后在这个新的主键上创建一个外键，并在子表中匹配尽可能多的记录.然后您设置了连接，您可以将清理作为辅助操作.

One way "around" this is set up a new primary key on the parent table. You then create a foreign key on this new primary key and match as many records as you can in the child table. You then have the join set up and you can go about cleaning as a secondary operation.

哪个更好?新主键还是使用复合键?这实际上取决于数据的性质，但我更喜欢使用派生键而不是自然键或复合键.但是，有时获取单个字段派生密钥所需的工作量很大.

Which is better? New primary key or working with the composite key? This really depends on the nature of the data, but I am more fond of using a derived key over a natural key or a composite key. But, there are times where the work necessary to get a single field derived key is a lot of work.

这篇关于在 SQL Server 2008 中创建复合外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！