MySQL Fundamentals: Understanding Foreign Keys

What is a Foreign Key?

A foreign key is a column or set of columns in one table that refers to the primary key in another table. This relationship enforces that the value in the foreign key column must match an existing value in the referenced primary key column.

This constraint ensures that relationships between tables remain valid and that no orphaned records exist.

• Foreign key columns must have the same data type as the referenced primary key.
• They enforce referential integrity by restricting invalid data entry.
• Foreign keys can reference primary keys or unique keys.

Creating Foreign Keys in MySQL

In MySQL, foreign keys are defined using the FOREIGN KEY constraint during table creation or by altering an existing table.

The syntax specifies the foreign key column and the referenced table and column.

• Foreign keys require both tables to use the InnoDB storage engine.
• The referenced column must be indexed (usually a primary key).
• You can specify actions on update or delete, such as CASCADE or SET NULL.

Foreign Key Example

Consider two tables: `departments` and `employees`. Each employee belongs to a department, so the `employees` table has a foreign key referencing the `departments` table.