using self referencing in sql server
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 EmpSalaryHistoryVALUES (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)
I created this example for self refernce key for 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);