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

问题描述

这个问题与这个问题非常相似，但对于 SQL服务器 2005:

This question is pretty much similar to this one, but for SQL Server 2005 :

我的数据库中有 2 个表:

I have 2 tables in my database:

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

每个库都由其唯一 ID 和应用程序名称标识.我正在努力确保每个内容都正确引用了现有库.

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.

创建约束时(使用向导)

When creating the constraint (using the Wizard) as

Primary key table            Foreign key table
[Libraries]                  [Content]
ID                  --->     LibraryID
Application         --->     Application

我有以下错误:

表 'Libraries' 中的列不匹配现有的主键或唯一约束

你知道发生了什么吗?如果有可能使用 SQL Server?(我根本无法修改[Library]表)

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)

非常感谢您的帮助！

推荐答案

当然可以创建与复合(多列)主键的外键关系.您没有向我们展示您用来尝试建立这种关系的语句 - 它应该是这样的:

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)

那是你用的吗??如果 (ID, Application) 确实是 dbo.Libraries 上的主键，则此语句肯定有效.

Is that what you're using?? If (ID, Application) is indeed the primary key on dbo.Libraries, this statement should definitely work.

Luk:只是为了检查 - 你能在你的数据库中运行这个语句并报告输出是什么吗??

Luk: just to check - can you run this statement in your database and report back what the output is??

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

这篇关于外键引用 SQL Server 中的 2 列主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！