Answered by:
Creating Foreign keys

Question
-
Hi
Created table
create table countrytable
(
Countryid int identity(1,1)
,Name varchar(100)
,CountryCode nvarchar(10)
,constraint pk primary key(Countryid,CountryCode)
)I am trying to create a table Employeeextension
create table EmployeeExtension
(
EmpExtnID int identity(1,1) constraint pk_empextnid primary key
,EmpID int not null constraint fk_empid foreign key references Employeetable(EmployeeID)
,CurrentCountryCode nvarchar(10) constraint fk_currentcountry foreign key references countrytable(CountryCode)
,RelievingDate datetime default null
,BrandPromotionDate datetime default null
)I am getting this error..
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'countrytable' that match the referencing column list in the foreign key 'fk_currentcountry'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.Am I doing any mistake please help me....
Thanks in advance
Monday, December 31, 2012 7:59 AM
Answers
-
You have a composite PK on Countryid,CountryCode but in EmployeeExtension table you are referencing to CountryCode only , is that possible to
have a PK on CountryCode in countrytable and having an UNIQUE constraint on Countryid,CountryCode in order to create FK in EmployeeExtension table?
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 Satheesh Variath Monday, December 31, 2012 8:58 AM
- Marked as answer by Allen Li - MSFT Monday, January 7, 2013 2:05 AM
Monday, December 31, 2012 8:02 AM -
Thanks for ur reply...
I got it....
I have given unique and not null constraint to country code column and added in employeeextension table as foreign key
create table countrytable
(
Countryid int identity(1,1)
,Name varchar(100)
,CountryCode varchar(10) not null constraint cn_unique unique
,constraint pk primary key(Countryid)
)create table EmployeeExtension
(
EmpExtnID int identity(1,1) constraint pk_empextnid primary key
,EmpID int not null constraint fk_empid foreign key references Employeetable(EmployeeID)
,CurrentCountryCode varchar(10) constraint fk_currentcountry foreign key references countrytable(CountryCode)
,RelievingDate datetime default null
,BrandPromotionDate datetime default null
)
Thanks...
- Marked as answer by Allen Li - MSFT Monday, January 7, 2013 2:05 AM
Monday, December 31, 2012 8:14 AM
All replies
-
You have a composite PK on Countryid,CountryCode but in EmployeeExtension table you are referencing to CountryCode only , is that possible to
have a PK on CountryCode in countrytable and having an UNIQUE constraint on Countryid,CountryCode in order to create FK in EmployeeExtension table?
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 Satheesh Variath Monday, December 31, 2012 8:58 AM
- Marked as answer by Allen Li - MSFT Monday, January 7, 2013 2:05 AM
Monday, December 31, 2012 8:02 AM -
No We cannot have a primary key on countrycode in countrytable..But it must be referenced as foreign key in employeeextension tableMonday, December 31, 2012 8:10 AM
-
Thanks for ur reply...
I got it....
I have given unique and not null constraint to country code column and added in employeeextension table as foreign key
create table countrytable
(
Countryid int identity(1,1)
,Name varchar(100)
,CountryCode varchar(10) not null constraint cn_unique unique
,constraint pk primary key(Countryid)
)create table EmployeeExtension
(
EmpExtnID int identity(1,1) constraint pk_empextnid primary key
,EmpID int not null constraint fk_empid foreign key references Employeetable(EmployeeID)
,CurrentCountryCode varchar(10) constraint fk_currentcountry foreign key references countrytable(CountryCode)
,RelievingDate datetime default null
,BrandPromotionDate datetime default null
)
Thanks...
- Marked as answer by Allen Li - MSFT Monday, January 7, 2013 2:05 AM
Monday, December 31, 2012 8:14 AM -
Then
create table countrytable
(
Countryid int identity(1,1)
,Name varchar(100)
,CountryCode nvarchar(10)
,constraint pk primary key(Countryid,CountryCode)
)
I am trying to create a table Employeeextension
create table EmployeeExtension
(
EmpExtnID int identity(1,1) constraint pk_empextnid primary key
,CountryCode nvarchar(10)
,Countryid int
,RelievingDate datetime default null
,BrandPromotionDate datetime default null
)
ALTER TABLE dbo.EmployeeExtension
ADD CONSTRAINT MyConstraint
FOREIGN KEY (Countryid,CountryCode)
REFERENCES dbo.countrytable(Countryid,CountryCode)
drop table EmployeeExtension,countrytableBest 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
Monday, December 31, 2012 8:17 AM -
Here is my suggestion:
create table countrytable ( Countryid tinyint identity(1,1) ,Name varchar(100) ,CountryCode varchar(10) not null constraint cn_unique unique ,constraint pk primary key(Countryid) ) create table EmployeeExtension ( EmpExtnID int identity(1,1) constraint pk_empextnid primary key ,EmpID int not null constraint fk_empid foreign key references Employeetable(EmployeeID) ,CountryID tinyint constraint fk_currentcountry foreign key references countrytable(CountryID) ,RelievingDate datetime default null ,BrandPromotionDate datetime default null )
Kalman Toth SQL 2008 GRAND SLAM
New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012- Edited by Kalman Toth Monday, December 31, 2012 9:29 AM
Monday, December 31, 2012 9:28 AM -
Hello Sanam,
You are getting that error because you have composite primary key. To qualify a foriegn key that key needs to be a primary or unique key column.
Please use Marked as Answer if my post solved your problem and use Vote As Helpful, if a post was useful to help other user's find a solution quicker.
Wednesday, January 2, 2013 2:19 PM