外键引用 SQL Server 中的 2 列主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3178709/
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
Foreign key referencing a 2 columns primary key in SQL Server
提问by Luk
This question is pretty much similar to this one, but for SQL Server 2005 :
这个问题是非常相似，这一个，但对于SQL Server 2005：
I have 2 tables in my database:
我的数据库中有 2 个表：
--'#' denotes the primary key
[Libraries]
#ID #Application Name
1 MyApp Title 1
2 MyApp Title 2
[Content]
#ID Application LibraryID Content
10 MyApp 1 xxx
11 MyApp 1 yyy
(the database is obviously much more complex and having this double key makes sense)
（数据库显然要复杂得多，拥有这个双键是有道理的）
Each library is identified by its unique ID and Application name. I'm trying to ensure that each content is properly referencing an existing library.
每个库都由其唯一 ID 和应用程序名称标识。我试图确保每个内容都正确地引用了现有的库。
When creating the constraint (using the Wizard) as
创建约束时（使用向导）作为
Primary key table Foreign key table
[Libraries] [Content]
ID ---> LibraryID
Application ---> Application
I have the following error:
我有以下错误：
The columns in table 'Libraries' do not match an existing primary key or UNIQUE constraint
表 'Libraries' 中的列与现有主键或 UNIQUE 约束不匹配
Do you have any idea of what is going on? and if it's possible at all using SQL Server? (I can't modify the [Library] table at all)
你知道发生了什么吗？如果有可能使用 SQL Server？（我根本无法修改 [Library] 表）
Thanks a lot for your help!
非常感谢你的帮助！
回答by marc_s
Of course it's possible to create a foreign key relationship to a compound (more than one column) primary key. You didn't show us the statement you're using to try and create that relationship - it should be something like:
当然，可以创建与复合（多列）主键的外键关系。你没有向我们展示你用来尝试建立这种关系的语句 - 它应该是这样的：
ALTER TABLE dbo.Content
ADD CONSTRAINT FK_Content_Libraries
FOREIGN KEY(LibraryID, Application)
REFERENCES dbo.Libraries(ID, Application)
Is that what you're using?? If (ID, Application)is indeed the primary key on dbo.Libraries, this statement should definitely work.
你用的是那个？？如果(ID, Application)确实是 上的主键dbo.Libraries，则此语句肯定有效。
Luk: just to check - can you run this statement in your database and report back what the output is??
Luk：只是为了检查 - 你能在你的数据库中运行这个语句并报告输出是什么吗？
SELECT
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
ccu.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.TABLE_NAME = tc.TABLE_NAME AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE
tc.TABLE_NAME IN ('Libraries', 'Content')
回答by Will Russell
Note that the fields must be in the same order. If the Primary Key you are referencing is specified as (Application, ID) then your foreign key must reference (Application, ID) and NOT (ID, Application) as they are seen as two different keys.
请注意，字段的顺序必须相同。如果您引用的主键指定为 (Application, ID)，那么您的外键必须引用 (Application, ID) 和 NOT (ID, Application)，因为它们被视为两个不同的键。
回答by danny
The key is "the order of the column should be the same"
关键是“列的顺序应该是一样的”
Example:
例子：
create Table A (
A_ID char(3) primary key,
A_name char(10) primary key,
A_desc desc char(50)
)
create Table B (
B_ID char(3) primary key,
B_A_ID char(3),
B_A_Name char(10),
constraint [Fk_B_01] foreign key (B_A_ID,B_A_Name) references A(A_ID,A_Name)
)
the column order on table A should be --> A_IDthen A_Name; defining the foreign key should follow the same order as well.
表 A 上的列顺序应该是 --> A_IDthen A_Name; 定义外键也应该遵循相同的顺序。
回答by Adrian Godong
The Contenttable likely to have multiple duplicate Applicationvalues that can't be mapped to Libraries. Is it possible to drop the Applicationcolumn from the LibrariesPrimary Key Index and add it as a Unique Key Index instead?
该Content表可能有多个Application无法映射到的重复值Libraries。是否可以Application从Libraries主键索引中删除该列并将其添加为唯一键索引？
回答by Alex France
I had the same problem and I think I have the solution.
我遇到了同样的问题，我想我有解决方案。
If your field Applicationin table Libraryhas a foreign key that references a field in another table (named ApplicationI would bet), then your field Applicationin table Libraryhas to have a foreign key to table Applicationtoo.
如果Application表Library中的字段有一个引用另一个表中的字段的外键（命名为Application我敢打赌），那么Application表中的字段Library也必须有一个表的外键Application。
After that you can do your composed foreign key.
之后，您可以执行组合外键。
Excuse my poor english, and sorry if I'm wrong.
请原谅我糟糕的英语，如果我错了，请原谅。
