引用 SQL Server 中的 2 列主键的外键 [英] Foreign key referencing a 2 columns primary key in SQL Server

问题描述

这个问题与this one非常相似，但对于 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

我有以下错误:

表库"中的列与现有主键不匹配或唯一约束

The columns in table 'Libraries' do not match an existing primary key or UNIQUE constraint

你知道发生了什么吗?以及是否有可能使用 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 列主键的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！