MySQL FOREIGN KEY

MySQL FOREIGN KEY Constraint

The FOREIGN KEY constraint prevents actions that could disrupt relationships between tables.

It involves a field or set of fields in one table that references the PRIMARY KEY in another table.

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

Consider the following two tables:

Persons Table

PerssonID	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

Note that the “PersonID” column in the “Orders” table references the “PersonID” column in the “Persons” table.

The “PersonID” column in the “Persons” table serves as the PRIMARY KEY.

In contrast, the “PersonID” column in the “Orders” table acts as a FOREIGN KEY.

This constraint ensures data integrity by restricting entries in the foreign key column to values present in the parent table.

FOREIGN KEY on CREATE TABLE

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

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

To assign a name to a FOREIGN KEY constraint and define it for multiple columns, use the following SQL syntax:

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)
);

FOREIGN KEY on ALTER TABLE

To add a FOREIGN KEY constraint to the “PersonID” column in an existing “Orders” table, use the following SQL:

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

To name a FOREIGN KEY constraint and define it for multiple columns, use the following SQL syntax:

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

DROP a FOREIGN KEY Constraint

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

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;