Loading...

Foreign Key is a reference key of Primary Key that is used to create a relationship between two table. 
In simple words we can say that, a Foreign Key in one table used to point Primary Key in another table.

Let us take an example to explain it:
Here we took two table, one is Student and another is Library. Students issued some books from Library.

Table Student

Student_ID

Student_Name

Father_Name

DOB

Gender

1

Rakesh Kumar

Mr. Upendra Singh

12-Jul-91

M

2

Suman Kumari

Mr. Rohit Pathak

10-Mar-90

F

3

Anil Yadav

Mr. Sunil Yadav

18-Mar-92

M

4

Ravi Singh

Mr. Vivek Singh

15-Dec-91

M

5

Prakash Kumar

Mr. Suraj Singh

23-Oct-93

M

6

Vijay Verma

Mr. Mohan Verma

1-Feb-90

M



Table Library

Issued_ID

Book_ID

Student_ID

Issued_Date

1

B-01

1

12-Jul-18

2

B-05

1

10-Mar-18

3

B-03

3

18-Mar-18

4

B-03

4

15-Dec-18

5

B-04

1

23-Oct-18

6

B-06

3

1-Feb-18



Look into both table 

  1. Student table Student_ID is a Primary Key
  2. Library table Student_ID is Foreign Key
Also in library table can have one more Foreign Key i.e. Book_ID. In library table have also an another reference of Book_ID from Book table (Note: Book table is taken in example) 

Note: if we try to insert a value in Student_ID of Library table that Student_ID is not exist in Student table then it will raise an error for constraint. It means, we can say relationship between two tables also provide us a surety of valid data.


SQL Query to add Foreign Key when creating new table

Create table Library
(  
Issued_ID int NOT NULL PRIMAY KEY,  
Book_ID int NOT NULL,  
Student_ID int FOREIGN KEY REFERENCES Student (Student_ID),
Issued_Date datetime


SQL Query to add Foreign Key on existing table 

Alter table Library
Add constraint fk_StudentLibrary
Foreign Key (Student_ID)
References Student(Student_ID)


SQL Query to drop Foreign Key constraint 

Alter table Library
Drop constraint fk_StudentLibrary