Foreign Key

Introduction

Real applications rarely keep all their data in one single table. A school database might have a students table and a separate grades table, and the two need a reliable way to stay connected. That connection is built using a foreign key.

In this lesson, you will learn what a foreign key is, build a worked example linking two tables, and see what MySQL does when a foreign key rule would be broken.

What is a Foreign Key?

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It creates a link between the two tables, ensuring that the value stored actually corresponds to a real row in the other table.

For example, if a grades table stores a student_id for every grade, a foreign key ensures that every student_id used actually matches a real, existing student in the students table — you cannot record a grade for a student who does not exist.

Worked Example: Linking Two Tables

Let's build a students table and a grades table, where each grade references exactly one student through a foreign key.

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(2)
);

CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

Query OK, 0 rows affected
Query OK, 0 rows affected

The FOREIGN KEY (student_id) REFERENCES students(id) clause tells MySQL: every value stored in grades.student_id must match an existing value in students.id.

INSERT INTO students (id, name, age, grade) VALUES (1, 'Alex', 16, 'A');
INSERT INTO students (id, name, age, grade) VALUES (2, 'Sam', 17, 'B');

INSERT INTO grades (id, student_id, subject, score) VALUES (1, 1, 'Math', 92);
INSERT INTO grades (id, student_id, subject, score) VALUES (2, 2, 'Science', 88);

Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected
Query OK, 1 row affected

What Happens on an Invalid Reference

Now let's try inserting a grade for a student_id that does not exist in the students table.

INSERT INTO grades (id, student_id, subject, score)
VALUES (3, 99, 'History', 75);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`grades`, CONSTRAINT `grades_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`))

MySQL rejects the insert because there is no student with id 99 in the students table. This is the foreign key constraint doing its job — it refuses to let the grades table reference a student that does not exist, keeping the two tables consistent with each other.

ON DELETE CASCADE

By default, MySQL also prevents you from deleting a row from students if any grade in the grades table still references it, since that would leave behind a grade pointing at a student that no longer exists.

Sometimes, though, you want related rows to be cleaned up automatically. Adding ON DELETE CASCADE to a foreign key tells MySQL: if the referenced student row is deleted, automatically delete every grade row that referenced it too.

CREATE TABLE grades (
    id INT PRIMARY KEY,
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id)
        REFERENCES students(id)
        ON DELETE CASCADE
);

With this in place, deleting a student automatically removes their grades along with them, instead of blocking the delete or leaving orphaned rows behind.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

A foreign key is how two tables stay meaningfully connected. By requiring every referenced value to actually exist in the parent table, MySQL prevents broken, dangling relationships between your data.

In this lesson, you learned what a foreign key is, built a students and grades example linked by student_id, saw what happens on an invalid reference, and previewed ON DELETE CASCADE. Next, you will learn about the UNIQUE key constraint.