Foreign key referencing a 2 columns primary key in SQL Server

Question

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!

Answer 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.

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