I recently got the following error: There are no primary or candidate keys in the referenced table 'Table Name' that match the referencing column list in the foreign key 'Foreign Key Constraint Name'.
This occurred when I tried to create a new table (TableB) in add a foreign key (TableB.ColSubID) to another table. In the another table (let say TableA) the referenced column in the foreign key is part of the primary key. However, that Primary Key is something
like this: PRIMARY KEY (TableA.ColID, TableA.ColSubID).
But, I would like to know why would be the reason of creating that UNIQUE CONSTRAINT in a column that is part of the PRIMARY KEY and at the same time works like a FOREIGN KEY.
Lesthad_mk
Member
98 Points
161 Posts
SQL Server Error Messages - Msg 1776 (There are no primary or candidate keys...)
Nov 22, 2012 04:36 PM|LINK
Hi all
I recently got the following error: There are no primary or candidate keys in the referenced table 'Table Name' that match the referencing column list in the foreign key 'Foreign Key Constraint Name'.
This occurred when I tried to create a new table (TableB) in add a foreign key (TableB.ColSubID) to another table. In the another table (let say TableA) the referenced column in the foreign key is part of the primary key. However, that Primary Key is something like this: PRIMARY KEY (TableA.ColID, TableA.ColSubID).
I found an article where there is a solution for this case, creating a UNIQUE INDEX/CONSTRAINT. http://www.sql-server-helper.com/error-messages/msg-1776.aspx
But, I would like to know why would be the reason of creating that UNIQUE CONSTRAINT in a column that is part of the PRIMARY KEY and at the same time works like a FOREIGN KEY.
I appreciet your help on this
Regards!
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: SQL Server Error Messages - Msg 1776 (There are no primary or candidate keys...)
Nov 22, 2012 07:39 PM|LINK
To be truly effective, a foreign key in one table must be the entire primary key in another, not just part of it.
Lesthad_mk
Member
98 Points
161 Posts
Re: SQL Server Error Messages - Msg 1776 (There are no primary or candidate keys...)
Nov 22, 2012 10:07 PM|LINK
Hi Dan
Thanks for your comment. I was reading a few articles about that. And them mention what you are saying.
Once again thanks
Cheers!!! ;-)