SQL FOREIGN KEY

In this tutorial, we will learn how to use FOREIGN KEY using SQL.

SQL FOREIGN KEY Constraint

• SQL FOREIGN KEY constraint is used to link two tables together and it is also termed as a referencing key.
• SQL FOREIGN KEY constraint is a field or collections of fields in one table that refers to the PRIMARY KEY constraint in another table.
• The table that contains PRIMARY KEY is termed as parent table or referenced table, and a table containing the FOREIGN KEY is termed as the child table.

Example:

Let us consider these two tables  "Customer" and "Orders" for records.

Table Name: Customer

Table Name: Orders

Here that column "CustID" in the table "Orders" points to the column "CustID" in the table "Customer".

Column "CustID" in the table "Customer" is the PRIMARY KEY in the table "Persons".

Column "CustID" in the table "Orders" is the FOREIGN KEY in the table "Orders".

FOREIGN KEY constraint prevents invalid data being inserted into the FOREIGN KEY column because it has to be one of the values contained in the table it points to.

A FOREIGN KEY constraint is also used to ensures that the links between tables would not be destroyed.

SQL FOREIGN KEY to CREATE TABLE Statement

To add a FOREIGN KEY when the table is created, a statement is as follow:

SQL Server / Oracle

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    CustID int FOREIGN KEY REFERENCES Customer(CustID)
);

MySQL

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustID) REFERENCES Persons(CustID)
);

SQL FOREIGN KEY on Multiple Columns

To define a FOREIGN KEY constraint on multiple columns use this syntax, a statement is as follow:

MySQL / SQL Server / Oracle - use this syntax

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_CustmOrder FOREIGN KEY (CustID)
    REFERENCES Customer(CustID)
);

SQL FOREIGN KEY on ALTER TABLE Statement

To add FOREIGN KEY constraint after creating the table, a statement is as follow:

SQL Server / MySQL / Oracle

ALTER TABLE Orders
ADD FOREIGN KEY(CustID) REFERENCES Customer(CustID);

To add FOREIGN KEY constraint after creating the table on the multiple columns, a statement is as follow:

ALTER TABLE Orders
ADD CONSTRAINT FK_CustmOrder
FOREIGN KEY (CustID) REFERENCES Customer(CustID);

SQL DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, a statement is as follow:

SQL Server / Oracle

ALTER TABLE Orders
DROP CONSTRAINT FK_CustmOrder;

MySQL

ALTER TABLE Orders
DROP FOREIGN KEY FK_CustmOrder;