Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Foreign key referencing a 2 columns primary key in SQL Server

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!

like image 870
Luk Avatar asked Jul 05 '10 10:07

Luk


People also ask

Can a foreign key reference 2 columns?

MySQL allows us to add a FOREIGN KEY constraint on multiple columns in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.

Can a foreign key reference two primary keys?

That's impossible. A FOREIGN KEY constraint can only point to one table and each table can only have one PRIMARY KEY constraint.

Can there be 2 columns of primary key?

The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).


1 Answers

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') 
like image 188
marc_s Avatar answered Sep 26 '22 05:09

marc_s