SanjayDVG SanjayDVG - 6 months ago 87
SQL Question

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint

I have created table Employee

Create table Employee
(
FName varchar(20) Not Null,
LName varchar(20) Not Null,
SSN int Not Null,
BDate Datetime,
Address varchar(50),
Sex char(1),
Salary money,
Super_SSN int,
Primary Key(SSN),
Foreign Key(Super_SSN) references Employee(SSN)
)


When i try to insert first row to ,

insert into Employee(FName,LName,SSN,BDate,Address,Sex,Salary,Super_SSN)
values('John','Smith',12345,'1965-01-09','Houston,TX','M',30000,33344)


I am getting the error like..

Error:


The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_Employee_Employee". The conflict occurred in database "Company", table "dbo.Employee", column 'SSN'.

Answer

You need to first INSERT record for SSN '33344' with Super_SSN value as NULL.

INSERT INTO  Employee(FName,LName,SSN,BDate,Address,Sex,Salary,Super_SSN)
VALUES (<FName>,<LName>,'33344',<BDate>,<Address>,<Sex>,<Salary>,NULL)

After that insert

INSERT INTO Employee (FName,LName,SSN,BDate,Address,Sex,Salary,Super_SSN)
VALUES ('John','Smith',12345,'1965-01-09','Houston,TX','M',30000,33344)

If SSN '33344' have any Super_SSN, update the SSN value (this record should be available in table).

Comments