SQL Constraint-Foreign Key Constraint - DotNet and DataBase

Wednesday, 1 August 2018

SQL Constraint-Foreign Key Constraint




The foreign key is to restrict the table from the junk data. While inserting some data into a table, the foreign key constraint will check whether the data is acceptable or not.
Let us suppose there are two tables "Employees, Department" and Employee table has a column called "EmpDept". These "EmpDept" should exist in "Department" table otherwise the IDs become homeless. In case someone tried to enter data directly in Employees table through a query, there is a chance to get enter junk ids in "EmpDept" column. To overcome this we can use "ForeignKey" concept.

Example:


CREATE TABLE Department(
ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
DeptNAME VARCHAR(50)
);

CREATE TABLE [dbo].[Employees](
       [EmpID] [int] NOT NULL PRIMARY KEY IDENTITY(1,1),
       [Empname] [varchar](100) NULL,
       [EmpDept] [int] NULL
       FOREIGN KEY (EmpDept) REFERENCES Department(ID)
);

In the above example,  "EmpDeptis the foreign key which does not allow to insert departmentids which are not there in "Department" table.

Alter Command

ALTER TABLE Employees
ADD FOREIGN KEY (EmpDeptREFERENCES Department(ID);

Drop Foreign Key

ALTER TABLE Employees
DROP CONSTRAINT <ForeignKey ID>;


No comments:

Post a Comment

x

Get Updates On

Discussion updates

Straight Into Your INBOX!

Enter your email address to subscribe to this website and receive notifications of new posts by email.