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:

StudentIDStudent_NameAddressCity
1NithinBank streetHyderabad
2BharathWhite FieldBangalore
3GouthamCourt roadChennai


Order Table:

Order_IDOrder NoStudentID
15848483
25586551
35746863
45632152

 

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 NULLPRIMARY 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 CONSTRAINTforeign_key
FOREIGN KEY(Student_ID)
REFERNCES Student (Student_ID)

 

How to Drop a FOREGIN 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 KEYFOREIGN KEY
It is always uniqueIt can be duplicated
It cannot be nullIt can be null
Only one primary key for a tableCan have more than one foreign key for a table
It uniquely identify a row in a tableForeign Key is a field which is primary key in another table