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 <strong>int</strong> NOT <strong>NULL</strong>,
OrderNo <strong>int</strong> NOT <strong>NULL</strong>,
P_Id <strong>int</strong>,
PRIMARY KEY (Order_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
Order_Id <strong>int</strong> NOT <strong>NULL</strong> PRIMARY KEY,
OrderNo <strong>int</strong> NOT <strong>NULL</strong>,
P_Id <strong>int</strong> 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 <strong>int</strong> NOT <strong>NULL</strong>,
OrderNo <strong>int</strong> NOT <strong>NULL</strong>,
P_Id <strong>int</strong>,
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 the 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
  • Primary key can’t contain null values.
  • Primary key can’t be duplicate.
  • A table can have only one primary key.
  • Primary key automatically adds a clustered index.
Foreign key
  • Foreign key can contain null values.
  • Foreign key can be duplicate.
  • A table can have more than one foreign key.
  • Foreign key not add any index automatically.