I am working on a project in which I need to create a table with composite primary key.
Till now no issues, but I need to use one of the column in the composite primary key as foreign key in another table.
There I was strucked.
Following is the error I am getting:-
Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'temp1' that match the referencing column list in the foreign key 'FK__tempMarks__sid__6FE99F9F'. Msg 1750, Level 16, State 0, Line 1 Could not create constraint. See previous errors.
It means that the tables and keys you are using for creating the constreaint do not match. Please check that the temp1 table column used by you as referece key is a primary key
The relationship always works on Primary and Forkeign Keys
Since, in your first table primary key is composite key with col1 and col2, you can't use col1 or col2 for primay key and foreign key relationship. It is mandatory to match all columns of Primary Key with the columns of referencing table
Primary Key and Foreign Key Relationship
==================================
Invalid : Table1.col1 = Table2.col1
Invalid : Table1.col2 = Table2.col2
Valid : Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2
My actual problem is table1 contains composite primary key (lets take c1 & c2)
I have another table in that column c2 which is a foreign key referring c2 of table1.
But I am unable to add foreign key constraint on table2.
The only way to do this is to put a UNIQUE constraint on table1.c2 which makes c2 an Alternate Key. Then you can create a foreign key on table2.c2 alone.
If table1.c2 is not UNIQUE, then you cannot create a foreign key to it, and it would be bad design to attempt to do so.
-Tab Alleman
Marked as answer by Chen Yu - MSFT on Nov 29, 2011 05:08 AM
c.j.nageswar...
Member
84 Points
28 Posts
Unable to create foreign key - referring to composite primary key's column
Nov 22, 2011 05:03 AM|LINK
Hi Experts,
I am working on a project in which I need to create a table with composite primary key.
Till now no issues, but I need to use one of the column in the composite primary key as foreign key in another table.
There I was strucked.
Following is the error I am getting:-
jassi_singh
Participant
1713 Points
419 Posts
Re: Unable to create foreign key - referring to composite primary key's column
Nov 22, 2011 05:55 AM|LINK
Hello,
It means that the tables and keys you are using for creating the constreaint do not match. Please check that the temp1 table column used by you as referece key is a primary key
c.j.nageswar...
Member
84 Points
28 Posts
Re: Unable to create foreign key - referring to composite primary key's column
Nov 22, 2011 07:38 AM|LINK
Hi,
I am using the same column names in both tables.
My actual problem is table1 contains composite primary key (lets take c1 & c2)
I have another table in that column c2 which is a foreign key referring c2 of table1.
But I am unable to add foreign key constraint on table2.
sandeepmitta...
Contributor
6801 Points
1059 Posts
Re: Unable to create foreign key - referring to composite primary key's column
Nov 22, 2011 11:12 AM|LINK
The relationship always works on Primary and Forkeign Keys
Since, in your first table primary key is composite key with col1 and col2, you can't use col1 or col2 for primay key and foreign key relationship. It is mandatory to match all columns of Primary Key with the columns of referencing table
Primary Key and Foreign Key Relationship
==================================
Invalid : Table1.col1 = Table2.col1
Invalid : Table1.col2 = Table2.col2
Valid : Table1.col1 = Table2.col1 and Table1.col2 = Table2.col2
Sandeep Mittal | My Blog - IT Developer Zone
TabAlleman
All-Star
15575 Points
2702 Posts
Re: Unable to create foreign key - referring to composite primary key's column
Nov 22, 2011 02:51 PM|LINK
The only way to do this is to put a UNIQUE constraint on table1.c2 which makes c2 an Alternate Key. Then you can create a foreign key on table2.c2 alone.
If table1.c2 is not UNIQUE, then you cannot create a foreign key to it, and it would be bad design to attempt to do so.