Foreign key referencing a 2 columns primary key in SQL Server?

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

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

I was using the UI directly at that point, but using your syntax raises the following error: "There are no primary or candidate keys in the referenced table 'dbo. Libraries' that match the referencing column list in the foreign key... – Luk Jul 5 '10 at 11:24 Well, in that case, the pair (Id, Application) is not the primary key on the Libraries table. You can only reference the primary key on a parent table - or a column (or set of columns) that are part of a unique index.

Check your Libraries table! – marc_s Jul 5 '10 at 14:48 That was my first thought too, but the SQL script specifies CREATE TABLE Libraries (...` CONSTRAINT PK_sf_Libraries PRIMARY KEY CLUSTERED ( Application ASC, ID ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY`... (sorry for dumping all the SQL here) – Luk Jul 5 '10 at 15:07 @Luk: something must be off here - either that script wasn't executed, or the table Libraries was modified since, or you have a problem with table names, or you're in the wrong database or something.... if Libraries really has that primary key, then the FK reference ought to work. – marc_s Jul 5 '10 at 15:18 1 @Luk: added a statement you could run for me on your database - just to check..... – marc_s Jul 5 '10 at 15:29.

The Content table likely to have multiple duplicate Application values that can't be mapped to Libraries. Is it possible to drop the Application column from the Libraries Primary Key Index and add it as a Unique Key Index instead?

Yes, maybe duplicates for Application but that's never required to be unique. The pair of (LibraryID, Application) can even have multiple values - that's not the PK on the Content table - it's the PK on the Libraries table! – marc_s Jul 5 '10 at 10:35 OK, so what happens when you have (LibraryID, Application) from Content that does not correspond to (ID, Application) on Libraries?

As of now, there's no constraint to enforce that. If OP don't want to change the current structure, I would suggest him to try creating a Unique Key index on (LibraryID, Application) and see if it can be successfully created. Only then, try creating the relationship again.

– Adrian Godong Jul 5 '10 at 10:51 Unfortunately, I can't touch Libraries at all. The two tables are even supposed have a 1-1 relationship because I can't add new columns to Libraries. You are right, a Unique index is required there (but that didn't fix the problem) – Luk Jul 5 '10 at 12:08 Am I wrong thinking I can only reference columns in a Foreign Key?

– Luk Jul 5 '10 at 12:11 Luk: you can reference two columns in an FK. Have you checked whether all row in Contents can be mapped to Libraries? (a simple LEFT JOIN will determine whether you can do this) – Adrian Godong Jul 5 '10 at 12:31.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.