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.
