SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint establishes a
link between data in two
tables, and prevents action that will destroy the link between them.
A FOREIGN KEY is a field in a table that refers to
the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Look at the following two tables:
Persons Table:
| PersonID | LastName | FirstName | Age |
|---|---|---|---|
| 1 | Hansen | Ola | 30 |
| 2 | Svendson | Tove | 23 |
Orders Table:
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Here we see that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.
The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is the
FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,
because it has to be one of the values from the parent table.
FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
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)
);MySQL:
CREATE TABLE Orders
(
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);FOREIGN KEY on Multiple Columns
To allow naming of a FOREIGN KEY constraint, and for defining a
FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
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)
);FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);FOREIGN KEY on Multiple Columns
To allow naming of a FOREIGN KEY constraint, and for defining a
FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);Drop a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
