aspose file tools*
The moose likes JDBC and the fly likes How To add two or more foreign keys in single table in sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How To add two or more foreign keys in single table in sql" Watch "How To add two or more foreign keys in single table in sql" New topic
Author

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

upanshu vaid
Ranch Hand

Joined: Sep 07, 2012
Posts: 73

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

Joined: Oct 27, 2005
Posts: 19720
    
  20

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:


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
jQuery in Action, 2nd edition
 
subject: How To add two or more foreign keys in single table in sql