WEB DEVELOPER SITE
HTMLCSSJAVASCRIPTSQLPHPBOOTSTRAPJQUERYANGULARXML
 

SQL FOREIGN KEY Constraint


SQL FOREIGN KEY constraint

The FOREIGN KEY in one table points to a UNIQUE KEY (the only constraint key) in another table.

Let's explain the foreign key with an example. Please see 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" table78:

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 "Persons" table.

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

The "P_Id" column in the "Orders" table is the FOREIGN KEY in the "Orders" table.

FOREIGN KEY constraints are used to prevent behavior that breaks joins between tables.

FOREIGN KEY constraint can also prevent illegal data from being inserted into a foreign key column, because it must be one of the values in the table it points to.


SQL FOREIGN KEY constraint when CREATE TABLE

The following SQL creates a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table 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 a FOREIGN KEY constraint and define FOREIGN KEY constraints 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)
)


ALTER TABLE Seasonal SQL FOREIGN KEY constraint

When the "Orders" table has been created, to create a FOREIGN KEY constraint in the "P_Id" 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 a FOREIGN KEY constraint and define FOREIGN KEY constraints 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)


Revoke FOREIGN KEY constraint

To remove 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