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

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