SQL FOREIGN KEY

SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint serves to inhibit actions that could disrupt the relationships between tables.

A FOREIGN KEY is a field or group of fields in one table that references the PRIMARY KEY in another table.

The table containing the foreign key is termed the child table, while the table containing the primary key is referred to as the referenced or parent table.

Consider the following two tables:

Persons Table

PersonID	LastName	FirstName	Age
1	Hansen	Ola	30
2	Svendson	Tove	23
3	Pettersen	Kari	20

Orders Table

OrderID	OrderNumber	PersonID
1	77895	3
2	44678	3
3	22456	2
4	24562	1

Observe that the “PersonID” column in the “Orders” table corresponds to the “PersonID” column in the “Persons” table.

In the “Persons” table, the “PersonID” column acts as the PRIMARY KEY.

In the “Orders” table, the “PersonID” column functions as a FOREIGN KEY.

The FOREIGN KEY constraint ensures that only valid data can be inserted into the foreign key column, as it must correspond to one of the values present in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The provided SQL statement establishes a FOREIGN KEY on the “PersonID” column during the creation of the “Orders” table.

MySQL:

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

SQL Server / Oracle / MS Access:

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

You can name a FOREIGN KEY constraint and define it on multiple columns using the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

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

SQL FOREIGN KEY on ALTER TABLE

If you need to establish a FOREIGN KEY constraint on the “PersonID” column after the “Orders” table has been created, utilize the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To enable the naming of a FOREIGN KEY constraint and define it across multiple columns, employ the subsequent SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To remove a FOREIGN KEY constraint, utilize the following SQL:

MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;