Usage and examples of the SQL FOREIGN KEY keyword

SQL Keywords Reference

FOREIGN KEY

FOREIGN KEY constraints are the key to connecting two tables together.

FOREIGN KEY is a field (or a collection of fields) in a table that references the PRIMARY KEY in another table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates the FOREIGN KEY 'Orders' on the column 'PersonID' when creating the table 'PersonID':

MySQL:

CREATE TABLE Orders
(
　　　
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
　　　
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
;

SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
　　　
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
　　　
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
;

To name a FOREIGN KEY constraint and define a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders
(
　　　
OrderID int NOT NULL,
　　　
OrderNumber int NOT NULL,
　　　
PersonID int,
　　　
PRIMARY KEY (OrderID),
　　　
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
　　　
REFERENCES Persons(PersonID)
;

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint named 'Orders' on the 'PersonID' column after the table has been created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);

To name a FOREIGN KEY constraint and define a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);

Delete FOREIGN KEY constraint

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

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

SQL Keywords Reference