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

问题描述

这个问题与这一个非常相似，但是对于SQL Server 2005：

我在我的数据库中有两个表：

pre $ - '＃'表示主键
[库]
#ID＃应用程序名称
1 MyApp标题1
2 MyApp标题2


[内容]
#ID应用程序库ID内容
10 MyApp 1 xxx
11 MyApp 1 yyy


（数据库显然要复杂得多，而且这个双键有意义）

每个库都由其唯一的ID应用名称。我试图确保每个内容正确地引用一个现有的库。
$ b

创建约束（使用向导）作为

 主键表外键表
 [库] [内容] 
 ID ---> LibraryID 
应用程序--->申请
  

我有以下错误：

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

你有什么想法吗？如果可以的话，所有使用SQL Server？ （我根本无法修改[Library]表）

感谢您的帮助！

解决方案

当然，可以创建一个到一个化合物（多个列）主键的外键关系。您没有向我们展示您正在使用的语句来尝试创建这种关系 - 它应该是这样的：

  ALTER TABLE dbo.Content 
 ADD CONSTRAINT FK_Content_Libraries 
 FOREIGN KEY（LibraryID，Application）
 REFERENCES dbo.Libraries（ID，Application）
  

这就是你正在使用的？如果（ID，Application）确实是 dbo.Libraries 上的主键， p>

Luk：只要检查一下 - 你可以在你的数据库中运行这个语句并且报告输出结果是什么？

 选择
 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'，'内容'）
  

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

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)

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

I have the following error:

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

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)

Thanks a lot for your help!

解决方案

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.

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屋！