SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
FOREIGN KEY in one table points to another table PRIMARY KEY.
Let's use an example to explain the foreign key. Consider the following two tables:
"Persons" 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 |
Please note, "Orders" table "P_Id" column point "Persons" table "P_Id" column.
"Persons" table "P_Id" column is the "Persons" table PRIMARY KEY.
"Orders" table "P_Id" column is the "Orders" table FOREIGN KEY.
FOREIGN KEY constraint is used to prevent damage to the connection between the behavior of the table.
FOREIGN KEY constraint also prevents illegal data into a foreign key column, it must be one because it points to the table.
SQL FOREIGN KEY constraint CREATE TABLE when
The following SQL when the "Orders" table is created to create a FOREIGN KEY constraint on the "P_Id" column:
MySQL:
(
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:
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
To name FOREIGN KEY constraint, and define a plurality of column FOREIGN KEY constraint, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
(
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 constraint ALTER TABLE when
When the "Orders" table has been created for columns create FOREIGN KEY constraint "P_Id", please use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To name FOREIGN KEY constraint, and define a plurality of column FOREIGN KEY constraint, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
Undo FOREIGN KEY constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:
DROP CONSTRAINT fk_PerOrders