在 sql server 中使用自引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5442693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using self referencing in sql server
提问by sqlchild
create table EMP(Eid int primary key)
insert into EMP values(11e3)
--self referencing
--自引用
alter table EMP
add constraint fk_EMP_Eid
foreign key (Eid) references EMP(Eid)
--now insert
--现在插入
insert into EMP values(12e2)
But, this insert should fail, because there is no previous value of Eid=1200in the EMP table, so when the foreign key will reference this column , then it would not find the value, hence should fail the insert .
但是，这次插入应该失败，因为Eid=1200在 EMP 表中没有以前的值，所以当外键将引用此列时，它将找不到该值，因此插入应该失败。
but why does it succeeds?
但是为什么会成功呢？
回答by Martin Smith
The column references itself.
该列引用自身。
So the addition of the row itself guarantees that there is a matching row. This constraint can never fail.
所以行本身的添加保证了有匹配的行。这种约束永远不会失败。
In fact looking at the execution plan SQL Server realises this and doesn't even bother checking it. There is no assertoperator present.
事实上，查看执行计划 SQL Server 意识到了这一点，甚至不去检查它。没有assert操作员在场。
If we create a more typical Employee table there are different plans for the inserts that can violate the constraint as below.
如果我们创建一个更典型的 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*/
If you try multiple rows a blocking spoolis added to the plan so the constraints aren't checked until all rows are inserted.
如果您尝试多行，则会在计划中添加一个阻塞假脱机，因此在插入所有行之前不会检查约束。
insert into EMP2 values (3,2),(4,3) /*Can violate constraint - multiple rows*/
And just for completeness as it was raised in the comments, looking at the case when the insert is to a table with a FK referencing a different one...
并且只是为了完整性，正如它在评论中提出的那样，查看插入到带有 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)
In this instance no spool is added to the plan it can check as it inserts each row rather than all at the end so it can rollback earlier in the event that a row fails (the end result will be the same)
在这种情况下，没有将线轴添加到计划中，它可以在插入每一行而不是最后插入所有行时进行检查，以便在行失败的情况下可以更早地回滚（最终结果将相同）
回答by Brian Ball
Your FK column fk_EMP_Eid probably allows nulls, therefore the relationship isn't required to exist, but if you do try to put a value in that column, then SQL Server will verify that the FK is valid or else it will error.
您的 FK 列 fk_EMP_Eid 可能允许空值，因此不需要存在关系，但如果您确实尝试在该列中放置值，则 SQL Server 将验证 FK 是否有效，否则会出错。
回答by Dimitris Kougioumtzis
I created this example for self refernce key for ms sql server
我为 ms sql server 的自引用密钥创建了这个示例
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);
