Recursive or self-referencing Foreign Key in sql server

The recursive foreign key is a foreign key that refers back to the primary key in the same table where a child and parent table are the same. When we reference the same table at the same time of pulling data.

For example, in an employee table, we have employees and managers. Managers are employee.

Let’s see the example.

I am creating a table.

CREATE TABLE tblEmp  (   EmpID INT PRIMARY KEY NOT NULL                 ,EmpName VARCHAR(25)                 ,Title VARCHAR(25)                 ,ManagerID INT                 ,HireDate DATETIME                 ,DepartmentID INT                         CONSTRAINT FK_Emp_Manager FOREIGN KEY (ManagerID) REFERENCES tblEmp(EmpID) )

In this table, we have created foreign key on the ManagerID column because Managers are also an employee.

Now I am inserting some records.

INSERT INTO tblEmp VALUES  (1, 'Bagesh', 'President', NULL, '05/10/2015',10); INSERT INTO tblEmp VALUES(2, 'Kumar', 'IT Manager', 1, '05/01/2016',20); INSERT INTO tblEmp VALUES(3, 'Bagi', 'Finance Manager', 2, '12/01/2015',  30); INSERT INTO tblEmp VALUES(4, 'Rajesh', 'Sales Manager', 2, '07/08/2015',  40); INSERT INTO tblEmp VALUES(5, 'Mahesh', 'BI Developer', 3, '09/02/2017',  20); INSERT INTO tblEmp VALUES(6, 'Ganesh', 'Data Analyst', 3, '02/25/2016',  20); INSERT INTO tblEmp VALUES(7, 'Raju', 'Accountant', 4, '02/01/2015',  30); INSERT INTO tblEmp VALUES(8, 'Ravi', 'Accounting Assistant', 4, '09/28/2016', 30); INSERT INTO tblEmp VALUES(9, 'Amit', 'Salesman', 5, '05/03/2015',  40); INSERT INTO tblEmp VALUES(10, 'Gopal', 'Salesman', 5, '06/03/2015', 40);

Records in the table.

       

Let’s see how we will get the employee ID with their manager's name.

SELECT e.EmpID  ,e.EmpName AS Employee  ,e.Title  ,e.ManagerID  ,m.EmpName AS Manager FROM tblEmp AS e  LEFT JOIN tblEmp AS m ON e.ManagerID = m.EmpID ORDER BY e.EmpID

      

Now we are inserting an employee and we assign a manager id which is not an employee, in this case, we will get below error.