SQL FOREIGN KEY

The FOREIGN KEY is used to define a relationship between two tables and a FOREIGN KEY in one table points to the PRIMARY KEY in another table.

Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:

MySQL:

CREATE TABLE Orders ( Order_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (Order_Id), FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )

SQL Server / Oracle / MS Access:

CREATE TABLE Orders ( Order_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) )

Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP FOREIGN KEY fk_POrders

Syntax of FOREIGN KEY Constraint on one column with CREATE TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders ( Order_Id int NOT NULL, OrderNo int NOT NULL, P_Id int, PRIMARY KEY (Order_Id), CONSTRAINT fk_POrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) )

Syntax of FOREIGN KEY Constraint on one column with ALTER TABLE statement:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders ADD CONSTRAINT fk_POrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

DropFOREIGNKEY:

Use following syntax to delete the foreign key.

MySQL:

ALTER TABLE Orders DROP FOREIGN KEY fk_POrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders DROP CONSTRAINT fk_POrders

Difference between primary key and foreign key:

Primary key	Foreign key
1. Primary key can’t contain null values. 2. Primary key can’t be duplicate. 3. A table can have only one primary key. 4. Primary key automatically adds a clustered index.	1. Foreign key can contain null values. 2. Foreign key can be duplicate. 3. A table can have more than one foreign key. 4. Foreign key not add any index automatically.