• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How To add two or more foreign keys in single table in sql

 
upanshu vaid
Ranch Hand
Posts: 83
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ranch,
I am getting the following error in Microsoft SQL server 2008 R2 while creating 2 foreign keys in employee table
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_emp2". The conflict occurred in database "E2", table "dbo.DEPARTMENT", column 'Dnumber'.
I have a total of 2 tables with name EMPPLOYEE and DEPARTMENT with fields resp are

EMPLOYEE TABLE fields

Fname varchar(50) Unchecked
Minit char(10) Checked
Lname varchar(50) Unchecked
Ssn char(10) Unchecked
Bdate date Checked
Address varchar(50) Checked
Sex char(10) Checked
Salary decimal(10, 2) Checked
Super_ssn char(10) Checked
Dno int Unchecked


DEPARTMENT Table fields

Dname varchar(50) Unchecked
Dnumber int Unchecked
Mgr_ssn char(10) Unchecked
Mgr_start_date date Checked

where unchecked means value in table should not be null and checked means value can be null.

Primary key and foreign key i am creating

Employee table

Primary Key (Ssn)
Foreign key (Super_ssn)References EMPLOYEE(Ssn)
Foreign key (Dno)References DEPARTMENT(Dnumber)

Department table
Primary Key(Dnumber)
Foreign key (Mgr_ssn)References EMPLOYEE(Ssn)

kindly help on this

I must say that this ranch platform for queries is very wonderful.
Thanks
 
Rob Spoor
Sheriff
Pie
Posts: 20661
64
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
upanshu vaid wrote:I am getting the following error in Microsoft SQL server 2008 R2 while creating 2 foreign keys in employee table
Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_emp2". The conflict occurred in database "E2", table "dbo.DEPARTMENT", column 'Dnumber'.

Your EMPLOYEE table has at least one record with a value in the Dnumber column that refers to a non-existing record in the DEPARTMENT table. You should fix that first, then try again.

To find these records:
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic