Foreign Key Refers the Columns of Same Table

Joydeep Das

Dec. 30, 14 · Interview

34.7K Views

Introduction

We all know about the Foreign Key constraint. Here in this article, we are not going to discuss about the definition or implementation concept of Foreign Key. But here we are trying to discuss about the Foreign key that refers the another a column of same table.

What is That?

EMPID	EMPNAME	DESIGNATION	MANAGERID
101	Sudip Das	Manager	101
102	Joydeep Das	Group Lead	101
103	Sukamal Jana	Group Lead	105

Here EPID is the Primary Key and the MANAHERID is the foreign key which refers the EMPID of the same table.

In the above situation Employee ID 101 is in manager position, so its Manager ID is same 101. But for Employee ID 102 the Manager ID is 101 and we can insert data without any error.

But in case of Employee ID 103 if we provide the Manager ID 105 it gives us an error as no such employee id (105) is not present and Foreign key gives us an Error over there.

How we implement That

Step-1 [ The Base Table with Foreign Key References ]

 IF OBJECT_ID(N'dbo.tbl_EMPLOYEEMASTER', N'U')IS NOT NULL
   BEGIN
      DROP TABLE [dbo].[tbl_EMPLOYEEMASTER];
   END
GO
CREATE TABLE [dbo].[tbl_EMPLOYEEMASTER]
    (
        EMPID        INT          NOT NULL PRIMARY KEY,
        EMPNAME      VARCHAR(50)  NOT NULL,
        DESIGNATION  VARCHAR(50)  NOT NULL,
        MANAGERID    INT          NOT NULL,
        CONSTRAINT FK_MANAGERID_tbl_EMPLOYEEMASTER FOREIGN KEY(MANAGERID)
        REFERENCES [dbo].[tbl_EMPLOYEEMASTER](EMPID)
      );

Here just look at the definition of foreign key specially the REFERENCES section.

Step-2 [ Insert Firs Record ]

 INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (101, 'Sudip Das', 'Manager', 101);
GO

Here we not find any error as Manager ID is the Same as the Employee ID and Foreign key Satisfied.

Step-3 [ Insert Second Record ]

 INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (102, 'Joydeep Das', 'Group Lead', 101);

GO

Here we do not get any error as Manager ID 101 is present in the table and Foreign key satisfied.

Step-4 [ Insert Third Record ]

 INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
       (EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (103, 'Sukamal Jana', 'Group Lead', 104);     

GO

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_MANAGERID_tbl_EMPLOYEEMASTER". The conflict occurred in database "PRACTICE_DB", table "dbo.tbl_EMPLOYEEMASTER", column 'EMPID'.

The statement has been terminated.

Here it gives error as Manager ID 104 is not present in the Table.

Hope you like it.

Relational database Database

Published at DZone with permission of Joydeep Das. See the original article here.

Opinions expressed by DZone contributors are their own.

Foreign Key Refers the Columns of Same Table

Partner Resources

Comments

Likes