在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的记录，所以当外键引用该列时，它将找不到该值，应该导致插入失败。

但是为什么它还是成功了呢？

- sqlchild

---

当您将值12e2插入到您的EMP表中（以及其Eid列中），那么该值确实存在-因此引用得到满足... - marc_s

看起来你在同一列上进行自我引用非常奇怪...这真的没有太多意义。通常，你会有一个 Employee(ID INT, ReportsTo INT)，并且 ReportsTo 列会自我引用 Employee(ID)，以便你可以建模员工-->老板关系。但是在同一列上进行自我引用...你的情况是什么，你想要做什么？ - marc_s

@marc_s：不，先生。我没有事先在表中插入1200。或者您的意思是说在行插入后再添加fk约束？如果是的话，它会检查1200是否存在，答案是肯定的，所以它成功了，但这样会导致重复插入吗？ - sqlchild

我只是想学习关于自引用的知识，所以在一个简单的例子上尝试它，即在一个单列表上。 - sqlchild

@marc_s 先生，外键的工作方式是这样的吗：在传递插入查询时，SQL首先插入该值，然后键检查该值是否存在于父表中，如果不存在，则回滚插入。这就是fkey的工作方式吗？ - sqlchild

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*/

如果您尝试多行插入，计划将添加阻塞卷轴，因此直到所有行都插入后才会检查约束条件。

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)

在这种情况下，计划中不会添加缓存，它可以在插入每一行时进行检查，而不是在最后检查所有内容，因此如果某一行失败，它可以更早地回滚（最终结果将是相同的）。

- Martin Smith

---

外键的意思是如果父表中存在一个值，则将该值插入到子表中，否则不插入。因此，在这里，父表和子表均为 EMP。因此，它检查父表中是否存在 1200，答案是否定的，因此应该插入失败。这是正常工作吗？ - sqlchild

@sqlchild - 它检查插入行之后是否满足约束条件。 - Martin Smith

好的先生，所以如果父表和子表不同，例如：父表没有记录，那么在向子表插入值时，它首先会在子表中插入行，然后检查父表中是否存在该行，但是在这里它没有找到，因此它将回滚。我说得对吗？ - sqlchild

@sqlchild - 是的，没错。在这种情况下，它可以在插入行时进行检查，而不是在最后全部检查，因此如果某一行失败，它可以更早地回滚（最终结果将是相同的）。 - Martin Smith

@sqlchild：如果子行实际上是在查找父行之前插入的，那么可以很容易地通过在子表中使用标识列来验证。在插入之前检查子表的当前标识值（IDENT_CURRENT('tablename')），然后尝试插入违反外键约束的行，再次检查标识值。您会发现它已经增加了，这证明该行已被插入，但由于违反外键约束而导致内部事务被回滚。 - Andriy M

Answer 2

你的外键列fk_EMP_Eid可能允许为空，因此关系不是必须存在的，但如果你尝试在该列中放入一个值，那么SQL Server将验证该外键是否有效，否则会出现错误。

- Brian Ball

Answer 3

我创建了这个示例作为MS SQL服务器自引用键的参考。

CREATE TABLE Category (
   CategoryId int IDENTITY(1,1) not null,
   ParentId int null,
   CONSTRAINT PK_CategoryId PRIMARY KEY CLUSTERED (CategoryId),
   CONSTRAINT FK_ParentId FOREIGN KEY (ParentId) REFERENCES  Category(CategoryId),
   Title nvarchar(255) NOT NULL
);

insert into category(title)
values
('category1');

insert into category(title,parentid)
values
('category2',1);

- Dimitris Kougioumtzis