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
