SQL SERVER – Why would a table use its primary key as a foreign key to itself ?

There have been cases where you have to create a reference of a foreign key to a primary key of the same table. Well it is typically used to build a hierarchy structure. This scenario is not very common.

Example: Parent – Manager Hierarchical Problem

--

CREATE TABLE EmployeeDetails 
	              (
			 EmployeeId INT PRIMARY KEY
                        ,EmployeeName VARCHAR(50)
                        ,ManagerId INT
		      )

ALTER TABLE EmployeeDetails 
	ADD CONSTRAINT fk_employee FOREIGN KEY (ManagerId) REFERENCES EmployeeDetails(EmployeeId)

--

In the above case Foreign Key (ManagerId) from the table back to itself means all managers are also employees hence the ManagerId is actually an EmployeeId. It just that he is working as a manager in the company. Now let’s discuss some issues we face while inserting and deleting data from these kind of data structures.

INSERT CASE PROBLEM

Now in this type of scenario you cannot insert data directly. Check out the first example below-

--

INSERT INTO EmployeeDetails VALUES 
  ( 1 , 'Pawan' , 2 ) 


--

Now if you try to insert the above row in the table it will return an error (Foreign Key Constraint). That’s because we don’t have an employee with Employee Id = 2. Check out the error below-

Msg 547, Level 16, State 0, Line 19
The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint “fk_employee”. The conflict occurred in database “Pawan”, table “dbo.EmployeeDetails”, column ‘EmployeeId’.

The statement has been terminated.

Solution

In this case you have to insert employee and their manager information together.

--

INSERT INTO EmployeeDetails VALUES 
  ( 1 , 'Pawan' , 2 ) 
, ( 2 , 'Isha' ,  NULL )

--

In the above case Employee ID = 1 and Employee ID = 2 are inserted together that’s why we didn’t get any foreign key constraint errors and records get inserted successfully. Now if you have a scenario where two employees are reporting to each other then also we can use the above logic. Check the example below.

--

INSERT INTO EmployeeDetails VALUES 
  ( 3 , 'Ramesh' , 4 ) 
, ( 4 , 'Kangana' ,  3 )

--

Table ‘EmployeeDetails’. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(2 row(s) affected)

DELETE CASE PROBLEM

In delete case you have to delete the values together if there is a foreign key reference. If you try to delete the single entity you will get an error. Check out the example below-

--

DELETE FROM EmployeeDetails WHERE EmployeeId = 1 OR EmployeeId = 2

--

I hope you have enjoyed the tricks we use in these scenarios. Cheers, Thanks for reading !

-Pawan Khowal

MSBISkills.com