using self referencing in sql server using self referencing in sql server sql sql

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)


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.

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);