SQL FOREIGN KEY constraints
FOREIGN KEY in one table points to PRIMARY KEY in another table.
Let's use an instance to explain the foreign key. Take a look at the following two tables:
"Peoples" table:
| P_Id | LastName | FirstName | Address | City |
|---|---|---|---|---|
| 1 | Hansen | Ola | Timoteivn 10 | Sandnes |
| 2 | Svendson | Tove | Borgvn 23 | Sandnes |
| 3 | Pettersen | Kari | Storgt 20 | Stavanger |
"Orders" table:
| O_Id | OrderNo | P_Id |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Note that the "P_Id" column in the Orders table points to the "P_Id" column in the Peoples table.
The "10th" column P_Id the "Persons" table is primaryY KEY in the "Persons" table.
The "Foreign" column in the "Orders" P_Id the "Orders" table is the FOREIGN KEY in the "Orders" table.
FOREIGN KEY constraints are used to prevent the destruction of connections between tables.
The FOREIGN KEY constraint also prevents illegal data from being inserted into the foreign key column because it must be one of the values in the table it points to.
SQL FOREIGN KEY constraints at CREATE TABLE
The following SQL creates the FOREIGN KEY constraint on the P_Id the Orders table when it is created:
Mysql:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
) SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
) To name the FOREIGN KEY constraint and define the FOREIGN KEY constraint for multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
) SQL FOREIGN KEY constraints at ALTER TABLE
When the Orders table has been created, to create a FOREIGN KEY P_Id in the "Orders" column, use the following SQL:
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id) To name the FOREIGN KEY constraint and define the FOREIGN KEY constraint for multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access：
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id) Undo the FOREIGN KEY constraint
To undo the FOREIGN KEY constraint, 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