Sunday 28 December 2014

Foreign Key Refers the Columns of Same Table

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.



Posted by: MR. JOYDEEP DAS