Create and drop FOREIGN KEY CONSTRAINT on Table In SQL SERVER

Introduction:

What is foreign key is the most common question asked in interview from freshers. I have tried to explain foreign key in my article in detail i.e. definition, how to create it and implementation. 

What is a Foreign Key?

In simple words A FOREIGN KEY is field in one table which points to a PRIMARY KEY field in another table.

A foreign key is a column(s) in one table that references the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data.

The referenced table is called the parent table while the table with the foreign key is called the child table.

Implementation: Let’s understand the concept of foreign key constraint by implementing it on tables. For this we are going to create two table Dept_Table and Emp_table.

First create a parent Table "Dept_Table " containing primary key "Dept_Id_Pk" that will become foreign key in child Table "Emp_Table"

CREATE TABLE Dept_table

 (

                 Dept_Id_PK INT IDENTITY(1,1) PRIMARY KEY, --define primary key

                 Dept_Name varchar (50) NOT NULL

 )

• Insert some data in Dept_table table using following queries

It will be as: 

Dept_Id_Pk	Dept_Name
1	HR
2	Inventory
3	IT

Now create Child Table "Emp_table" containing foreign key "Dept_Id_Fk" which is the primary key in " Dept_Table " parent table

I have mentioned two ways to create foreign key in a table. You can use any of the two.

First way:

CREATE  TABLE Emp_Table

 (

                 Emp_Id INT IDENTITY(1,1) PRIMARY KEY, --define primary key

                 Emp_Name VARCHAR (50) NOT NULL,

                 Age       INT,

                 Dept_Id_Fk INT FOREIGN KEY REFERENCES  Dept_Table (Dept_Id_pk) --define foreign key

 )

Or Second way:

 CREATE TABLE Emp_Table

 (

                 Emp_Id INT IDENTITY(1,1) PRIMARY KEY, --define primary key

                 Emp_Name VARCHAR (50) NOT NULL ,

                 Age       INT,

                 Dept_Id_fk INT,      

                 CONSTRAINT FK_Emp_Table_ Dept_Table FOREIGN KEY (Dept_Id_Fk) REFERENCES Dept_Table (Dept_Id_pk) --define foreign key with name 

 )

Note: In first way we are not providing the name of our foreign key constraint . So it will automatically create a default name somewhat like "FK__Emp_Table__Dept_I__0EA330E9" for the foreign key constraint . But in second way we are specifying the name of the foreign key constraint (FK_Emp_Name_Dept_table).

Insert some data in Emp_table using following queries 

It will be as: 

Emp_Id	Emp_Name	Age	Dept_Id_Fk
1	ABC	25	1
2	XYZ	24	1
3	RST	26	3

Note: Dept_Id_Fk in Emp_table table is defined as foreign key which is the primary key of Dept_table table.

How to create FOREIGN KEY Constraint on existing table

Syntax is

ALTER TABLE ChildTableName ADD FOREIGN KEY (ColumnName)

REFERENCES  ParentTablePersons(ColumnName)

For example: 

ALTER TABLE Emp_Table ADD FOREIGN KEY (Dept_Id_fk)

REFERENCES  Dept_table (Dept_Id_Pk)

Or

Syntax is:

ALTER TABLE ChildTableName ADD CONSTRAINT ForeignKeyConstraintName FOREIGN KEY (ColumnName)

REFERENCES  ParentTablePersons(ColumnName)

For example:

ALTER TABLE Emp_Table ADD CONSTRAINT  FK_ Emp_Table _Dept_Table  FOREIGN KEY (Dept_Id_Fk)

REFERENCES Dept_Table (Dept_Id_Pk)

How to DROP a FOREIGN KEY Constraint

The syntax is

ALTER TABLE TableName

DROP CONSTRAINT ForeignKeyConstraintName

For example

ALTER TABLE Emp_Table

DROP CONSTRAINT FK_Emp_Table_Dept_table

Points to remember:

1) We cannot delete the record whose primary key is used as foreign key in other table.

2)   FOREIGN KEY Field value must either exist in the referenced column or it could be NULL; otherwise, it will give foreign key violation error.

3)  We can also reference unique key as foreign key.

4) We can link two table of same database by using Foreign key.

5) The data type or domain of the participating columns must be same.

6) We can’t add foreign key constraints on temporary tables ortable variables.