SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.

In simple words you 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 are two tables first one is students table and second is orders table.

Here orders are given by students.

SQL FOREIGN KEY constraint ON CREATE TABLE:

To create a foreign key on the "S_Id" column when the "Orders" table is created:

MySQL:

					CREATE TABLE orders   

(   

O_Id int NOT NULL,   

Order_No  int NOT NULL,   

S_Id int,   

PRIMAY KEY (O_Id),   

FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)   

)  

					
SQL Server /Oracle / MS Access:

					CREATE TABLE Orders   

(   

O_Id int NOT NULL PRIMAY KEY,   

Order_No int NOT NULL,   

S_Id int FOREIGN KEY REFERENCES persons (S_Id)   

)  

					

SQL FOREIGN KEY constraint for ALTER TABLE:

If the Order table is already created and you want to create a FOREIGN KEY constraint on the ?S_Id? column, you should write the following syntax:

Defining a foreign key constraint on single column: MySQL / SQL Server / Oracle / MS Access:

					ALTER TABLE Orders   

ADD CONSTRAINT fk_PerOrders   

FOREIGN KEY(S_Id)   

REFERENCES Students (S_Id) 

					

DROP SYNTAX for FOREIGN KEY COSTRAINT:

If you want to drop a FOREIGN KEY constraint, use the following syntax:

MySQL: ALTER TABLE Orders ROP FOREIGN KEY fk_PerOrders SQL Server / Oracle / MS Access: ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders

Difference between primary key and foreign key in SQL:

These are some important difference between primary key and foreign key in SQL-

Primary key cannot be null on the other hand foreign key can be null.

Primary key is always unique while foreign key can be duplicated.

Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.

There is only one primary key in the table on the other hand we can have more than one foreign key in the table.