在 SQL Server 2008 中创建复合外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6651667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a composite foreign key in SQL Server 2008
提问by Danielle
I have two tables that I'd like to create a foreign key for.
我有两个表，我想为其创建外键。
Primary Table
主表
PK - Key1 - varchar(20)
PK - Key2 - date
Secondary Table
辅助表
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.
主表中的列与主键或唯一约束不匹配。
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.
辅助表中可以有许多具有相同 Key1 和 Key2 的记录，因此我们将主键设为自动创建的数字。
Any thoughts on how I can set up the foreign key relationship between these two tables?
关于如何设置这两个表之间的外键关系的任何想法？
采纳答案by Gregory A Beamer
Some of this is focused, some of this is context for others having any sort of problem like this (like anyone actually searches first?)
其中一些是重点关注的，其中一些是其他人遇到此类问题的背景（就像任何人实际上首先搜索？）
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.
当您在创建键时遇到问题时，首先要检查的是确保两个表中的数据类型没有不匹配。如果你有一个 bigint 和另一个 int ，它会爆炸。这适用于所有键，但如果您使用多个字段，则更有可能突然出现。简单的数学说明了机会增加的原因。
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.
下一个问题是数据。如果由于数据原因无法创建key，则必须找出子表中存在哪些父表中不存在的内容。LEFT JOIN 表（连接的第二个/左侧的辅助表）并且只包括主表为空的行。您要么必须在父表中创建这些记录，要么删除它们。
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.
哪个更好？新主键还是使用复合键？这实际上取决于数据的性质，但我更喜欢使用派生键而不是自然键或复合键。但是，有时获取单个字段派生密钥所需的工作量很大。
回答by Optillect Team
A foreign key MUST refer to columns that compose a unique index (PK or UK) with the same number of column, their types and order. E.g.:
外键必须引用组成具有相同列数、类型和顺序的唯一索引（PK 或 UK）的列。例如：
CREATE TABLE PrimaryTable (
Key1 varchar(20),
Key2 date)
GO
ALTER TABLE PrimaryTable ADD CONSTRAINT PK
PRIMARY KEY (Key1, Key2)
GO
CREATE TABLE SecondaryTable (
AutoID int IDENTITY,
Key1 varchar(20),
Key2 date)
GO
ALTER TABLE SecondaryTable ADD CONSTRAINT FK
FOREIGN KEY (Key1, Key2) REFERENCES PrimaryTable (Key1, Key2)
GO
回答by Damien_The_Unbeliever
This will work:
这将起作用：
CREATE TABLE PTable (
Key1 varchar(20) not null,
Key2 date not null,
constraint PK_PTable PRIMARY KEY (Key1,Key2)
)
CREATE TABLE STable (
AutoID int IDENTITY(1,1) not null primary key,
Key1 varchar(20) not null,
Key2 date not null,
constraint FK_STable_PTable FOREIGN KEY (Key1,Key2) references PTable (Key1,Key2)
)
What you need to do is get Management Studio to script your tables and compare them to the above.
您需要做的是让 Management Studio 为您的表格编写脚本并将它们与上述表格进行比较。
回答by Zack Jannsen
There are some good answers here, but I would like to take this a step further - for the sake of posterity.
这里有一些很好的答案，但为了后代，我想更进一步。
A foreign key does have to reference either a Primary key(Unique, Clustered Index) or a Uniqueconstrained column in another table. Basically, the necessary component is the Uniqueconstraint. I would add that you can have nullable columns in your foreign key, BUT if you do allow nulls in a "composite" key SQL skips the verification of the data in the foreign key relationship. This is an important point to remember as the main reason most of us use foreign keys is to ensure data integrity across our databases.
外键必须引用另一个表中的Primary key（唯一、聚集索引）或受Unique约束的列。基本上，必要的组件是Unique约束。我想补充一点，您可以在外键中包含可为空的列，但是如果您确实允许“复合”键中的空值，SQL 将跳过外键关系中的数据验证。这是要记住的重要一点，因为我们大多数人使用外键的主要原因是为了确保我们数据库中的数据完整性。
On a final note, I like to explicitly declare all my key names. Why, you might ask? If you need to use "Full-Text Indexing" in the future for better search capabilities, not doing so forces you to reference all the "auto-generated" names of the keys. This may not be a big deal for small projects that do not require data transformation or scheduled Full-Text index updates, but if you are scripting this functionality you could make your job more difficult (e.g. having to look up the actual name of your Primary Key's default name: pk_someTable_1248594832828495904).
最后一点，我喜欢明确声明我所有的键名。为什么，你可能会问？如果您将来需要使用“全文索引”以获得更好的搜索能力，不这样做会迫使您引用所有“自动生成的”键名。对于不需要数据转换或预定全文索引更新的小型项目来说，这可能不是什么大问题，但是如果您编写此功能的脚本，您可能会使您的工作更加困难（例如，必须查找您的主节点的实际名称）键的默认名称：) pk_someTable_1248594832828495904。
Here is what I would do in writing the SQL to avoid any future pitfalls:
这是我在编写 SQL 时会做的事情，以避免任何未来的陷阱：
- Do not allow NULLs on Composite foreign keys if possible.
- Name keys explicitly using an agreed upon naming convention (e.g.
PK_Schema/56_TalbeName_Col1_Col2). Not only does this give you a standard name for the key, but you can easily see from the index what columns are referenced and in which order.
- 如果可能，不要在复合外键上允许 NULL。
- 使用商定的命名约定（例如
PK_Schema/56_TalbeName_Col1_Col2）显式命名键。这不仅为您提供了键的标准名称，而且您可以从索引中轻松查看引用了哪些列以及引用的顺序。
The Code:
编码：
CREATE TABLE MySchema.PrimaryTable (
Key1 varchar(20) NOT NULL,
Key2 date NOT NULL,
CONSTRAINT PK_MySchema_PrimaryTable_Key1_Key2 PRIMARY KEY (Key1, Key2)
)
GO
CREATE TABLE MySchema.SecondaryTable (
AutoID int IDENTITY,
Key1 varchar(20) NOT NULL,
Key2 date NOT NULL,
CONSTRAINT FK_MySchema_SecondaryTable_Key1_Key2
FOREIGN KEY (Key1, Key2) REFERENCES PrimaryTable (Key1, Key2)
)
GO
OptillectTeam is basically dead on with his answer. I just wanted to clarify a few important things that had not been mentioned before. There is a good reference on the MSDN sight discussing this and more on foreign keys: Foreign Key Constraint.
OptillectTeam 基本上已经接受了他的回答。我只是想澄清一些以前没有提到的重要事情。在 MSDN 上有一个很好的参考，讨论这个以及更多关于外键的内容： 外键约束。
回答by Abhishek Jaiswal
FOR ADDING UNIQUE KEY USE BELOW QUERY
用于在查询下方添加唯一键使用
ALTER TABLE [TableName] ADD UNIQUE ([Column1], [Column2]);
