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!

This is quite clumsy solution. For your task it's enough to have plain FK referencing Library from App. Where you get idea you need two columns?? — Vincent, Jun 14 '23 at 22:37
@Vincent 12 years ago me probably had a good reason, but today I would definitely not do a 2 column PK :) — Luk, Jun 16 '23 at 23:21

marc_s · Accepted Answer · 2010-07-05T15:29:42.517

79

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

edited Jul 05 '10 at 15:29

answered Jul 05 '10 at 10:26

marc_s

732,580
175
1,330
1,459

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 05 '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 05 '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 05 '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 05 '10 at 15:18
1

@Luk: added a statement you could run for me on your database - just to check..... – marc_s Jul 05 '10 at 15:29
Sorry, I was sure I had replied. I have checked and the primary keys seem correct. However, I have created a new database with only these two tables (in a clean, empty state) and I can create the key without any trouble. I guess there's something in the state of the database, unrelated to the matter at hand, that prevents me from creating the key. I'm gonna drop the matter from now, the problem is deeper than expected. Thanks! – Luk Jul 08 '10 at 15:16
2

Um, has anyone considered "Will Russell"'s theory in his "Mar 18 '14 at 14:43" Answer below? I know Luk said he got it to work in a "new database", but if he "was using the UI" (vs. modifying auto-generated Scripts) as he admitted doing so at least once for at least his original FK add attempt, there's a good chance he also did so in the "new database", and therefore there's a good chance that in the "new database", he *accidentally* got the order of the Columns consistent (both in the PK def in the Create Table and the FK def in the Alter Table). – Tom Aug 18 '17 at 22:13

score 14 · Answer 2 · answered Mar 18 '14 at 14:43

14

Note that the fields must be in the same order. If the Primary Key you are referencing is specified as (Application, ID) then your foreign key must reference (Application, ID) and NOT (ID, Application) as they are seen as two different keys.

answered Mar 18 '14 at 14:43

Will Russell

324
2
11

1

I suspect this is the problem also esp. since he listed "Application" first in his Primary Key def ("PRIMARY KEY CLUSTERED ([Application] ASC, [ID] ASC)") in his "Jul 5'10 at 15:07" Comment to the Accepted Answer ("marc_s's on "Jul 5 '10 at 10:26") but "ID" first in both of his examples in his OP. I'm surprised no one else (incl. the 500 (pinky-to-lips) THOUSAND more Rep'ed "marc_s") pointed that out in the 4 yrs. 8 mos. before you. – Tom Aug 18 '17 at 22:06
Oh my god I spent hours trying to figure this out and this turned out to be the answer. Thank you!!!! – codingguy3000 Feb 10 '21 at 01:32

score 6 · Answer 3 · edited May 19 '17 at 05:49

The key is "the order of the column should be the same"

Example:

create Table A (
    A_ID char(3) primary key,
    A_name char(10) primary key,
    A_desc desc char(50)
)

create Table B (
    B_ID char(3) primary key,
    B_A_ID char(3),
    B_A_Name char(10),
    constraint [Fk_B_01] foreign key (B_A_ID,B_A_Name) references A(A_ID,A_Name)
)

the column order on table A should be --> A_ID then A_Name; defining the foreign key should follow the same order as well.

score 1 · Answer 4 · answered Jul 05 '10 at 10:27

1

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?

answered Jul 05 '10 at 10:27

Adrian Godong

8,802
8
40
62

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 05 '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 05 '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 05 '10 at 12:08
Am I wrong thinking I can only reference columns in a Foreign Key? – Luk Jul 05 '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 05 '10 at 12:31
Content is empty for the moment, `ID` and `LibraryID` are a `uniqueidentifier NOT NULL` and `Application` is a `nvarchar(50) NOT NULL` on both sides... This is getting weird, I'll probably come back with a fresher head later. – Luk Jul 05 '10 at 13:52

score 0 · Answer 5 · edited Oct 03 '12 at 19:56

0

I had the same problem and I think I have the solution.

If your field Application in table Library has a foreign key that references a field in another table (named Application I would bet), then your field Application in table Library has to have a foreign key to table Application too.

After that you can do your composed foreign key.

Excuse my poor english, and sorry if I'm wrong.

edited Oct 03 '12 at 19:56

Marko

20,385
13
48
64

answered Aug 02 '12 at 00:18

Alex France

1

Foreign key referencing a 2 columns primary key in SQL Server

5 Answers5

Linked