sql - 在sql server中使用自引用

Question

create table EMP(Eid int primary key)

insert into EMP values(11e3)

--自引用

alter table EMP 
add constraint fk_EMP_Eid 
foreign key (Eid) references EMP(Eid)

--现在插入

insert into EMP values(12e2)

但是，这次插入应该会失败，因为 EMP 表中没有 Eid=1200 的先前值，因此当外键引用此列时，它将找不到该值，因此插入应该失败。

但是为什么它会成功呢？

Answer 1

该列引用自身。

因此行本身的添加保证了存在匹配的行。这个约束永远不会失败。

事实上，通过查看执行计划，SQL Server 就意识到了这一点，甚至懒得去检查它。不存在 assert 运算符。

如果我们创建一个更典型的 Employee 表，则不同的插入计划可能会违反如下约束。

create table EMP2(Eid int primary key, boss_id int null);
alter table EMP2 add constraint fk_EMP2_Eid 
 foreign key (boss_id) references EMP2(Eid)

insert into EMP2 values(1,null) /*Can't violate constraint as NULL*/
insert into EMP2 values(2,1)    /*Can violate constraint as NOT NULL*/

如果您尝试多行 blocking spool已添加到计划中，因此在插入所有行之前不会检查约束。

insert into EMP2 values (3,2),(4,3) /*Can violate constraint - multiple rows*/

只是为了完整性，正如评论中提出的那样，查看插入到具有引用不同表的 FK 的表时的情况...

CREATE TABLE EmpSalaryHistory
(
Eid INT NOT NULL REFERENCES EMP(Eid),
EffectiveDate DATETIME NOT NULL,
Salary INT,
PRIMARY KEY (Eid,EffectiveDate)
)

INSERT INTO EmpSalaryHistory
VALUES    (1,GETDATE(),50000),
          (2,GETDATE(),50000)

在这种情况下，没有假脱机被添加到计划中，它可以在插入每一行而不是最后插入所有行时进行检查，因此如果一行失败，它可以更早地回滚(最终结果将是相同的)