SQL Server Error Messages - Msg 1776 (There are no primary or candidate keys...) RSS

• • Reply
  

  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!

• • Reply
  

  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.

  • Marked as answer by Lesthad_mk on Nov 22, 2012 10:07 PM
• • Reply
  

  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!!! ;-)