Monday, December 31, 2012 7:47 AM
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
Monday, December 31, 2012 7:56 AMAnswerer
>>>How to solve this?
Create a PK on the column of referenced table, see example belowcreate 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 AMModerator
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
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
- Edited by HuaMin ChenMicrosoft Community Contributor Monday, December 31, 2012 11:44 AM
Monday, December 31, 2012 12:12 PM
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
- Proposed As Answer by David Gutter Wednesday, January 02, 2013 3:50 PM