locked
Creating Foreign keys RRS feed

  • 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

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

    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

    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 table
    Monday, 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...

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

    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