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

Answer 1

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

Answer 2

No We cannot have a primary key on countrycode in countrytable..But it must be referenced as foreign key in employeeextension table

Answer 3

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...

Answer 4

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,countrytable

---

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

Answer 5

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

Answer 6

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.