SQL Foreign Key
SQL FOREIGN KEY Constraint
In order to stop operations that would break connections between tables, the FOREIGN KEY constraint is employed.
A field (or group of fields) in one table that points to the primary key in another table is known as a FOREIGN KEY.
The table that has the main key is referred to as the referenced or parent table, while the table that has the foreign key is called the child table.
Persons Table
| PersonID | LastName | FirstName | Age |
|---|---|---|---|
| 1 | Hansen | Ola | 30 |
| 2 | Svendson | Tove | 23 |
| 3 | Pettersen | Kari | 20 |
Orders Table
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Observe that the “Orders” table’s “PersonID” column references the “Persons” table’s “PersonID” field.
The primary key of the “Persons” database is found in the “PersonID” column.
One of the FOREIGN KEYS in the “Orders” database is the “PersonID” column.
Because the foreign key value must match one of the entries in the parent table, the FOREIGN KEY constraint stops invalid data from being placed into the foreign key column.
SQL FOREIGN KEY on CREATE TABLE
When the “Orders” table is created, the following SQL establishes a FOREIGN KEY on the “PersonID” column:
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)
);
Use the following SQL syntax to define a FOREIGN KEY constraint on several columns and to allow naming of the constraint:
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
Use the following SQL to establish a FOREIGN KEY constraint on the “PersonID” column after the “Orders” table has been created:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
Use the following SQL syntax to define a FOREIGN KEY constraint on several columns and to allow naming of the constraint:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
Use this SQL to remove a FOREIGN KEY constraint:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
