Foreign Key Refers the Columns of Same Table
Foreign Key Refers the Columns of Same Table
Join the DZone community and get the full member experience.
Join For FreeGet the Edge with a Professional Java IDE. 30-day free trial.
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)
);
Step-2 [ Insert Firs Record ]
INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
(EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (101, 'Sudip Das', 'Manager', 101);
GO
INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
(EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (102, 'Joydeep Das', 'Group Lead', 101);
GO
Step-4 [ Insert Third Record ]
INSERT INTO [dbo].[tbl_EMPLOYEEMASTER]
(EMPID, EMPNAME, DESIGNATION, MANAGERID)
VALUES (103, 'Sukamal Jana', 'Group Lead', 104);
GO
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'.
Here it gives error as Manager ID 104 is not present in the Table.
Hope you like it.
Get the Java IDE that understands code & makes developing enjoyable. Level up your code with IntelliJ IDEA. Download the free trial.
Like This Article? Read More From DZone
Published at DZone with permission of Joydeep Das , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}