FOREIGN KEY

In this page:

Understanding FOREIGN KEY
Foreign Key Constraints
ON DELETE CASCADE
Adding Foreign Keys to Existing Tables
Dropping Foreign Keys

Understanding FOREIGN KEY

A FOREIGN KEY column in one table references a PRIMARY KEY column in another, which is how MySQL enforces that related data actually stays consistent across tables — you can't reference a customer that doesn't exist.

Example: Understanding FOREIGN KEY

sql

CREATE TABLE authors (id INT PRIMARY KEY);
CREATE TABLE books (
  id INT PRIMARY KEY,
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

Foreign Key Constraints

When inserting a row with a foreign key value, MySQL checks that the referenced row actually exists in the parent table first, and rejects the insert outright if it doesn't — this is called referential integrity.

Example: Foreign Key Constraints

sql

CREATE TABLE authors (id INT PRIMARY KEY);
CREATE TABLE books (
  id INT PRIMARY KEY,
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);
INSERT INTO books VALUES (1, 999); -- rejected: no author with id 999

ON DELETE CASCADE

ON DELETE CASCADE automatically deletes matching child rows whenever their parent row is deleted, which is convenient for cleanup but dangerous if applied somewhere data loss should require explicit confirmation instead.

Example: ON DELETE CASCADE

sql

CREATE TABLE authors (id INT PRIMARY KEY);
CREATE TABLE books (
  id INT PRIMARY KEY,
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(id) ON DELETE CASCADE
);

Adding Foreign Keys to Existing Tables

Adding a foreign key to tables that were created independently lets you retroactively formalize a relationship between them, though existing data must already satisfy the constraint or the ALTER TABLE will fail.

Example: Adding Foreign Keys to Existing Tables

sql

ALTER TABLE books ADD CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES authors(id);

Dropping Foreign Keys

Dropping a foreign key requires knowing its specific constraint name (visible via SHOW CREATE TABLE), since a table can have several foreign keys and MySQL needs to know exactly which one to remove.

Example: Dropping Foreign Keys

sql

SHOW CREATE TABLE books;
ALTER TABLE books DROP FOREIGN KEY fk_author;

🔒

Chapter Quiz — Complete all 8 topics to unlock

0/8 topics done

Complete these topics first:

← Previous Lesson PRIMARY KEY Next Lesson → DEFAULT Constraint