using self referencing in sql server

Question

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=1200 in the EMP table, so when the foreign key will reference this column , then it would not find the value, hence should fail the insert .

but why does it succeeds?

Answer 1

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 assert operator present.

If we create a more typical Employee table there are different plans for the inserts that can violate the constraint as below.

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 spool is 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...

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)