SQL FOREIGN KEY Constraint

« Previous Chapter Next Chapter »

SQL FOREIGN KEY Constraint

A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Persons" table:

P_Id LastName FirstName Address City
1 Karmen Kasa Los Angels - USA New Orleans
2 Jacob Mary Los Angels - USA New Orleans
3 Maxwell Glen Australia Sydney

The "Purchases" table:

PU_ID OrderNo P_Id
1 I-1567 3
2 I-6008 3
3 I-1110 2
4 I-5616 1

Note: "P_Id" column in the "Purchases" table points to the "P_Id" column in the "Persons" table.

The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "P_Id" column in the "Purchases" table is a FOREIGN KEY in the "Purchases" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

SQL FOREIGN KEY Constraint on CREATE TABLE

The Below SQL creates a FOREIGN KEY on the "P_Id" column when the "Purchases" table is created:

MySQL:

CREATE TABLE Purchases
(
PU_ID int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (PU_ID),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

SQL Server / Oracle / MS Access:

CREATE TABLE Purchases
(
PU_ID int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Purchases
(
PU_ID int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (PU_ID),
CONSTRAINT fk_PerPurchases FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

SQL FOREIGN KEY Constraint on ALTER TABLE

To create a FOREIGN KEY constraint on the "P_Id" column when the "Purchases" table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Purchases
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Purchases
ADD CONSTRAINT fk_PerPurchases
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

To DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Purchases
DROP FOREIGN KEY fk_PerPurchases

SQL Server / Oracle / MS Access:

ALTER TABLE Purchases
DROP CONSTRAINT fk_PerPurchases

« Previous Chapter Next Chapter »
Have Any Suggestion? We Are Waiting To Hear from YOU!

Your Query was successfully sent!