Over a million developers have joined DZone.

Foreign Key Refers the Columns of Same Table

· Java Zone

Microservices! They are everywhere, or at least, the term is. When should you use a microservice architecture? What factors should be considered when making that decision? Do the benefits outweigh the costs? Why is everyone so excited about them, anyway?  Brought to you in partnership with IBM.

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.


Discover how the Watson team is further developing SDKs in Java, Node.js, Python, iOS, and Android to access these services and make programming easy. Brought to you in partnership with IBM.

Topics:

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

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}