SQL Foreign Key

Foreign Key is a column used to point primary key in another table.
In relational Databases, a foreign key is used to establish connection between two tables


Let us take an example to explain it.

Student Table:

StudentID Student_Name Address City
1 Nithin Bank street Hyderabad
2 Bharath White Field Bangalore
3 Goutham Court road Chennai


Order Table:

Order_ID Order No StudentID
1 584848 3
2 558655 1
3 574686 3
4 563215 2

 

Here you can note that StudentID in Student table points to StudentID in the Order table.


The FOREIGN KEY constraint is used to prevent the actions that destroy links between tables and it also prevents to inserts invalid data.

 

How to create FOREIGN KEY Constraint?

To create a foreign key constraint on the StudentID when the Order table is created in the above example:


MySQL:


CREATE TABLE orders
(
Order_ID int NOT NULL,
Order No int NOT NULL,
Student ID int,
PRIMARY KEY(Order_ID),
FOREIGN KEY (Student_ID) REFERENCES Students (Student_ID)
)

 


SQL Server / Oracle / MS Access:


CREATE TABLE orders
(
Order_ID int NOT NULL PRIMARY KEY,
Order No int NOT NULL,
Student ID int FOREIGN KEY REFERENCES Students (Student_ID)
)

 

SQL FOREIGN KEY on ALTER TABLE:


If the Order table is already created and if you want to create a foreign key constraint on the Student_ID of order table it is written as follows:

MySQL / SQL Server / Oracle / MS Access:


ALTER TABLE Order
ADD CONSTRAINT foreign_key
FOREIGN KEY(Student_ID)
REFERENCES Student (Student_ID)

 

How to Drop a FOREIGN KEY constraint?

 

If you want to drop a FOREIGN KEY Constraint, the following syntax is to be followed:


MySQL:


ALTER TABLE Order
DROP FOREIGN KEY foreign_key

 


SQL Server / Oracle / MS Access:


ALTER TABLE Order
DROP CONSTRAINT foreign_key

 

Difference between Primary and Foreign Key

 

PRIMARY KEY FOREIGN KEY
It is always unique It can be duplicated
It cannot be null It can be null
Only one primary key for a table Can have more than one foreign key for a table
It uniquely identify a row in a table Foreign Key is a field which is primary key in another table