none
No unique index found for the referenced field of the primary table.

    Question

  • No unique index found for the referenced field of the primary table.    i go this error message when i try set the content of my foreign key sysId to the content of my primary key custSysId...
    Thursday, February 24, 2011 5:05 AM

Answers

  • For a foreign key constraint it's requiered that the referenced table do have a unique (primary) key, see MSDN FOREIGN KEY Constraints: "A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table"

    A foreign key can also reference columns of a unique index, even when that index does not support a primary key or unique constraint.  That said, it is rare in my experience to have a foreign key that references anything other than the primary key.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Wednesday, March 02, 2011 5:47 AM
    Thursday, February 24, 2011 1:08 PM

All replies

  • Hello,

    For a foreign key constraint it's requiered that the referenced table do have a unique (primary) key, see MSDN FOREIGN KEY Constraints: "A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table"


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Thursday, February 24, 2011 7:38 AM
  • For a foreign key constraint it's requiered that the referenced table do have a unique (primary) key, see MSDN FOREIGN KEY Constraints: "A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table"

    A foreign key can also reference columns of a unique index, even when that index does not support a primary key or unique constraint.  That said, it is rare in my experience to have a foreign key that references anything other than the primary key.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by KJian_ Wednesday, March 02, 2011 5:47 AM
    Thursday, February 24, 2011 1:08 PM
  • The error suggests that your Primary Key custSysId is in fact not a Primary Key. You should check the table structure (and constraints) of the referenced table, and make sure that you refer to the actual Primary Key column(s).

     


    Gert-Jan
    Friday, February 25, 2011 6:47 PM