MySQL FOREIGN KEY

MySQL FOREIGN KEY Constraint

In order to stop operations that would break connections between tables, the FOREIGN KEY constraint is employed.

A field (or group of fields) in one table that points to the primary key in another table is known as a FOREIGN KEY.

The table that has the main key is referred to as the referenced or parent table, while the table that has the foreign key is called the child table.

Examine the next 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 “Orders” table’s “PersonID” column references the “Persons” table’s “PersonID” field.

The PRIMARY KEY of the “Persons” database is found in the “PersonID” column.

One of the FOREIGN KEYS in the “Orders” database is the “PersonID” column.

Because the foreign key value must match one of the entries in the parent table, the FOREIGN KEY constraint stops invalid data from being placed into the foreign key column.

FOREIGN KEY on CREATE TABLE

When the “Orders” table is created, the SQL that follows establishes a FOREIGN KEY on the “PersonID” column:

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

Use the SQL syntax below to define a FOREIGN KEY constraint on several columns and to permit naming of a FOREIGN KEY constraint:

				
					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

Use the following SQL to establish a FOREIGN KEY constraint on the “PersonID” column after the “Orders” table has been created:

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

Use the SQL syntax below to define a FOREIGN KEY constraint on several columns and to permit naming of a FOREIGN KEY constraint:

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

DROP a FOREIGN KEY Constraint

Use this SQL to remove a FOREIGN KEY constraint:

				
					ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

MySQL FOREIGN KEY