外键引用SQL Server中的2列主键

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!

非常感谢你的帮助！

5 个解决方案

#1

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')

#2

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.

请注意，字段必须采用相同的顺序。如果您引用的主键指定为（应用程序，ID），则外键必须引用（应用程序，ID）和NOT（ID，应用程序），因为它们被视为两个不同的键。

#3

The Content table likely to have multiple duplicate Application values that can't be mapped to Libraries. Is it possible to drop the Application column from the Libraries Primary Key Index and add it as a Unique Key Index instead?

内容表可能具有多个无法映射到库的重复应用程序值。是否可以从库主键索引中删除“应用程序”列，并将其添加为唯一键索引？

#4

I had the same problem and I think I have the solution.

我有同样的问题，我想我有解决方案。

If your field Application in table Library has a foreign key that references a field in another table (named Application I would bet), then your field Application in table Library has to have a foreign key to table Application too.

如果表库中的字段Application具有引用另一个表中的字段的外键（名为Application I bet bet），那么表库中的字段Application也必须具有表Application的外键。

After that you can do your composed foreign key.

之后，您可以使用组合的外键。

Excuse my poor english, and sorry if I'm wrong.

请原谅我可怜的英语，对不起，如果我错了。

#5

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_ID then A_Name; defining the foreign key should follow the same order as well.

表A上的列顺序应为 - > A_ID，然后是A_Name;定义外键也应该遵循相同的顺序。

#1