sql - INSERT 语句与 FOREIGN KEY SAME TABLE 约束冲突

Question

我已经创建了表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)
)

当我尝试将第一行插入到时，

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

我收到类似错误..

错误:

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

您需要首先插入 SSN '33344' 记录，其中 Super_SSN 值为 NULL。

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

之后插入

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

如果SSN“33344”有任何Super_SSN，请更新SSN值(该记录应在表中可用)。