Using self-regulation in sql server

Question

create table EMP(Eid int primary key) insert into EMP values(11e3) 

- self-regulation

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

- now insert

 insert into EMP values(12e2) 

But this insertion should fail, because there is no previous value Eid=1200 in the EMP table, therefore, when the foreign key refers to this column, then it will not find the value, therefore, it should fail.

but why does it succeed?

Answer 1

The column refers to itself.

Thus, adding the line itself ensures that the corresponding line exists. This limitation can never fail.

In fact, looking at the execution plan, SQL Server understands this and does not even bother to check. The assert missing.

If we create a more typical Employee table, there are different plans for inserts that may violate the constraint, as shown 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 lines, a blocking coil is added to the plan, so restrictions are not checked until all lines 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 refers to a table with FK referencing another ...

 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 case, no coil is added to the plan, which it can check, since it inserts every row, not all at the end, so it can roll back earlier if the row fails (the end result will be the same)

Answer 2

Your FK column fk_EMP_Eid is probably null, so no relationship is required to exist, but if you try to put a value in this column, then SQL Server will check that FK is valid, otherwise it will fail.