FOREIGN KEY

Difficulty

Last Reviewed

Notes

Reference Link

Reviewed

Status

Not started

Introduction to MySQL foreign key

A foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.

Let’s take a look at the following customers and orders tables from the sample database.

The relationship between customers table and orders table is one-to-many. This relationship is established via the foreign key in the orders table specified by the customerNumber column.
The customerNumber column in the orders table links to the customerNumber primary key column in the customers table.
The customers table is called the parent table or referenced table, and the orders table is known as the child table or referencing table.
Typically, the foreign key columns of the child table often refer to the primary key columns of the parent table.
A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.
A table can only have a one primary key.
When a foreign key constraint is in place, it ensures that the values in the foreign key columns of the child table correspond to valid entries in the primary key columns of the parent table, or are set to NULL if allowed. This helps maintain referential integrity between the two tables.
Referential integrity ensures that relationships between tables remain consistent. For example, if a child table has a foreign key reference to a parent table, it must ensure that any value in the foreign key column either matches a value in the parent table's primary key column or is NULL (if allowed).
Foreign Key Constraint Actions: When defining a foreign key constraint, you can specify actions that should occur when a row in the parent table is updated or deleted. Common actions include:

SET NULL: If a row in the parent table is deleted or its primary key is updated, the corresponding foreign key values in the child table are set to NULL.
CASCADE: If a row in the parent table is deleted or its primary key is updated, the corresponding rows in the child table are also deleted or updated to match the new primary key. (dynamic)
RESTRICT: Prevents the deletion or update of a row in the parent table if there are corresponding rows in the child table.
NO ACTION: Similar to RESTRICT, it prevents the deletion or update of the parent row if there are matching foreign key values in the child table.
SET DEFAULT: Sets the foreign key value in the child table to a default value when the corresponding row in the parent table is deleted or updated.

💡

MySQL fully supports three actions: RESTRICT, CASCADE and SET NULL.

💡

If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRIC

‣

Self-Referencing Foreign key

‣

MySQL FOREIGN KEY syntax

‣

FOREIGN KEY

Introduction to MySQL foreign key

Self-Referencing Foreign key

MySQL FOREIGN KEY syntax

MySQL FOREIGN KEY examples