The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint

Question

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

Solution 1

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

Solution 2

The error is likely thrown because there is a foreign key from Super_SSN to SSN column. You cannot insert a value of 33344 into Super_SSN unless that value already exists in SSN. Try inserting null into Super_SSN or inserting user 33344 first.