Latest web development tutorials

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:

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 FOREIGN KEY constraint, and define a plurality of column FOREIGN KEY constraint, 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 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:

ALTER TABLE Orders
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:

ALTER TABLE Orders
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:

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders