MySQL FOREIGN KEY Constraint Tutorial

What is a FOREIGN KEY Constraint?

A FOREIGN KEY is a column or set of columns in one table that refers to the PRIMARY KEY in another table. It establishes a link between the two tables.

This constraint ensures that the value in the foreign key column matches an existing value in the referenced primary key column, preventing orphan records.

• Enforces referential integrity between tables.
• Prevents insertion of invalid foreign key values.
• Restricts deletion or update of referenced rows unless cascaded.

Defining FOREIGN KEY Constraints

You can define a FOREIGN KEY constraint when creating a table or by altering an existing table.

The syntax includes specifying the foreign key column(s), the referenced table, and the referenced column(s).

• Use `FOREIGN KEY (column_name) REFERENCES referenced_table(referenced_column)`.
• You can add `ON DELETE` and `ON UPDATE` actions to control behavior on changes.

Example: Creating Tables with FOREIGN KEY

Let's create two tables: `departments` and `employees`. Each employee belongs to a department, so `employees` will have a foreign key referencing `departments`.