SQL FOREIGN KEY Constraint

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to the PRIMARY KEY in another table.

Let's explain the FOREIGN KEY with an example. Please see the following two tables:

Table: 'Persons'

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

Table: 'Orders'

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1

Please note that the 'Id_P' column in 'Orders' points to the 'Id_P' column in the 'Persons' table.

The 'Id_P' column in the 'Persons' table is the PRIMARY KEY in the 'Persons' table.

The 'Id_P' column in the 'Orders' table is a FOREIGN KEY in the 'Orders' table.

FOREIGN KEY constraints are used to prevent actions that can destroy the connections between tables.

FOREIGN KEY constraints can also prevent illegal data from being inserted into the foreign key column, as it must be one of the values in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL creates a FOREIGN KEY for the 'Id_P' column when the 'Orders' table is created:

MySQL:

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Id_O int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
Id_P int FOREIGN KEY REFERENCES Persons(Id_P)
)

If you need to name the FOREIGN KEY constraint and define FOREIGN KEY constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

If you need to create a FOREIGN KEY constraint for the 'Id_P' column when the 'Orders' table already exists, please use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

If you need to name the FOREIGN KEY constraint and define FOREIGN KEY constraints for multiple columns, please use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)

Revoke FOREIGN KEY Constraint

To revoke the FOREIGN KEY constraint, please use the following SQL:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders