Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Error On Foreign Key Creation

Proposed Error On Foreign Key Creation

  • Monday, December 31, 2012 7:47 AM
     
     
    Sign In to Vote
    0

    I get This error message when I'm creating foreign key fro table even   reference table column  is primary key.

    "There are no primary or candidate keys in the referenced table 'countrytable' that match the referencing column list in the foreign key 'fk_currentcountrycode'."

    How to solve this?

    Thanks in advance

    Regards,

    BSMsbi

     

All Replies

  • Monday, December 31, 2012 7:56 AM
    Answerer
     
     Proposed
    Sign In to Vote
    0

    >>>How to solve this?

     Create a PK on the column of referenced table, see example below

    create table parent (id int) --no pk
    create table child (id int, parentid int foreign key references parent(id))  
    ----Msg 1776, Level 16, State 0, Line 1
    ----There are no primary or candidate keys in the referenced table 'parent' that match the referencing column list in the foreign key 'FK__child__parentid__7993D0C1'.
    ----Msg 1750, Level 16, State 0, Line 1
    ----Could not create constraint. See previous errors.


    drop table parent

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    • Proposed As Answer by Varsha Mishra Monday, December 31, 2012 8:37 AM
    •  
     
  • Monday, December 31, 2012 8:55 AM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    Make sure you are spelling the column names right.  Here is an example:

    ALTER TABLE [Banking].[Depositor]  WITH CHECK ADD FOREIGN KEY([AccountID])
REFERENCES [Banking].[Account] ([AccountID])
ON DELETE CASCADE;

    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

     
  • Monday, December 31, 2012 11:38 AM
     
     
    Sign In to Vote
    0

    Please

    1) Make sure that you do have one PK in country table for the current FK column.

    2) And also all FK values in your current table should exist in country table PK column.

    Many Thanks & Best Regards, Hua Min



     
  • Monday, December 31, 2012 12:12 PM
     
     Proposed
    Sign In to Vote
    0

    Hi,

    there might be 2-reasons of why you are getting this error message, because:

    1. you haven't create any primary key on your parent table yet  and trying to create a foreign key on your child table, or.

    2. you have created a composite primary key in your parent table and trying to relate it with your child table by not mentioning all the key columns (participated in your actually primary key).

    hope it helpful.

    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem
    --Kiran

    • Proposed As Answer by David Gutter Wednesday, January 02, 2013 3:50 PM
    •