SQLFOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
TheFOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
AFOREIGN KEY is a field (or collection of fields) in one table, that refers to thePRIMARY 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 |
| 3 | Pettersen | Kari | 20 |
Orders Table
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Notice 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 thePRIMARY KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is aFOREIGN KEY in the "Orders" table.
TheFOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,because it has to be one of the values contained in the parent table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates aFOREIGN KEY on the "PersonID" column when the "Orders" table is created:
MySQL:
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL Server / Oracle / MS Access:
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of aFOREIGN KEY constraint, and for defining aFOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
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 aFOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ADD FOREIGN KEY (PersonID)REFERENCES Persons(PersonID);
To allow naming of aFOREIGN KEY constraint, and for defining aFOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID)REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
To drop aFOREIGN KEY constraint, use the following SQL:
MySQL:
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
DROP CONSTRAINT FK_PersonOrder;
