Take a training institute's database. Each course has a course_id, and no two courses share one: that is a primary key, and it answers "which row is this?". Each batch also stores a course_id saying which course it belongs to: that is a foreign key, and it answers "which row in the other table does this point to?". The primary key protects identity inside one table. The foreign key protects the links between tables.
Differences in one table
| Primary key | Foreign key | |
|---|---|---|
| Purpose | Uniquely identifies each row | Links a row to a row in another (or the same) table |
| Per table | Exactly one (it may span several columns) | Any number |
| Duplicates | Not allowed | Allowed: many batches can point to one course |
| NULL | Not allowed | Allowed unless you add NOT NULL; NULL means "no link" |
| Index | Is the clustered index in InnoDB | MySQL needs an index on it and creates one if missing |
| Error when broken | 1062 duplicate, 1048 NULL | 1452 on the child side, 1451 on the parent side |
| Engine | Any | Enforced by InnoDB only |
Three linked tables
CREATE TABLE courses (
course_id INT PRIMARY KEY,
title VARCHAR(30) NOT NULL
);
CREATE TABLE batches (
batch_id INT PRIMARY KEY,
course_id INT NOT NULL,
start_date DATE,
CONSTRAINT fk_batch_course FOREIGN KEY (course_id)
REFERENCES courses(course_id)
ON DELETE RESTRICT ON UPDATE CASCADE
);
CREATE TABLE enrolments (
batch_id INT,
student_email VARCHAR(40),
PRIMARY KEY (batch_id, student_email), -- composite primary key
CONSTRAINT fk_enrol_batch FOREIGN KEY (batch_id)
REFERENCES batches(batch_id) ON DELETE CASCADE
);
INSERT INTO courses VALUES (1,'SQL for Analysts'), (2,'Python Basics');
INSERT INTO batches VALUES (11,1,'2026-12-01'), (12,1,'2027-01-05'), (21,2,'2026-12-08');
INSERT INTO enrolments VALUES
(11,'priya@example.com'), (11,'sahil@example.com'), (12,'priya@example.com'), (21,'ankit@example.com');
batch_id is the primary key of batches and also a foreign key inside enrolments. The same column name plays a different role in each table. In enrolments the primary key spans two columns: Priya can join many batches, but she cannot join the same batch twice.
What the primary key refuses
INSERT INTO courses VALUES (1, 'Excel');
INSERT INTO courses VALUES (NULL, 'Excel');
INSERT INTO enrolments VALUES (11, 'priya@example.com');
ERROR 1062 (23000): Duplicate entry '1' for key 'courses.PRIMARY'
ERROR 1048 (23000): Column 'course_id' cannot be null
ERROR 1062 (23000): Duplicate entry '11-priya@example.com' for key 'enrolments.PRIMARY'
What the foreign key refuses
From the child side, you cannot point at a course that does not exist:
INSERT INTO batches VALUES (31, 3, '2026-12-15');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(`pk`.`batches`, CONSTRAINT `fk_batch_course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ...)
From the parent side, you cannot delete a course that batches still use, because this key says ON DELETE RESTRICT:
DELETE FROM courses WHERE course_id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails ...
Referential actions: what happens to the children
The ON UPDATE and ON DELETE clauses decide what MySQL does to child rows when the parent changes.
ON UPDATE CASCADE
UPDATE courses SET course_id = 100 WHERE course_id = 1;
SELECT batch_id, course_id FROM batches ORDER BY batch_id;
| batch_id | course_id |
|---|---|
| 11 | 100 |
| 12 | 100 |
| 21 | 2 |
Both SQL batches followed the course to its new ID automatically. In practice, primary keys should rarely change. A meaningless surrogate key (an auto-increment number) never needs to.
ON DELETE CASCADE
DELETE FROM batches WHERE batch_id = 11;
SELECT batch_id, student_email FROM enrolments ORDER BY batch_id, student_email;
| batch_id | student_email |
|---|---|
| 12 | priya@example.com |
| 21 | ankit@example.com |
Deleting batch 11 silently removed its two enrolments. That suits rows that have no meaning without their parent. It is dangerous for anything you may need later, such as invoices or attendance, where RESTRICT is the safer choice.
| Action | Effect on child rows |
|---|---|
RESTRICT / NO ACTION | Refuse the parent change (the default; the two behave the same in InnoDB) |
CASCADE | Delete or update the children too |
SET NULL | Set the foreign key column to NULL (the column must allow NULL) |
Check what each key does with:
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME, UPDATE_RULE, DELETE_RULE
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'pk'
ORDER BY CONSTRAINT_NAME;
| CONSTRAINT_NAME | TABLE_NAME | REFERENCED_TABLE_NAME | UPDATE_RULE | DELETE_RULE |
|---|---|---|---|---|
| fk_batch_course | batches | courses | CASCADE | RESTRICT |
| fk_enrol_batch | enrolments | batches | NO ACTION | CASCADE |
Rules that trip people up
- Types must match.
INTcannot referenceINT UNSIGNED, and string keys need compatible character sets. A mismatch makes theCREATE TABLEorALTER TABLEfail. - The referenced column must be indexed, normally because it is a primary key or unique key. MySQL adds an index on the foreign key column itself;
SHOW INDEX FROM batcheslists one namedfk_batch_course. - Both tables must use InnoDB. MyISAM accepts the syntax and enforces nothing.
- Disabling checks lets bad data in. Imports often run with
SET FOREIGN_KEY_CHECKS = 0, and rows inserted that way are not checked afterwards. Find orphans with an anti-join:
SELECT b.batch_id, b.course_id
FROM batches b
LEFT JOIN courses c ON c.course_id = b.course_id
WHERE c.course_id IS NULL;
| batch_id | course_id |
|---|---|
| 99 | 42 |
Batch 99 was inserted with checks switched off and points to a course that does not exist.
Choosing a primary key
- Prefer a short, stable, meaningless value:
INTorBIGINT AUTO_INCREMENT. - Keep natural identifiers (email, PAN, roll number) as
UNIQUEcolumns rather than as the primary key. They can change, and they make every foreign key that copies them longer. - Use a composite primary key for pure link tables such as
enrolments.
All six constraint types are covered with examples in MySQL constraints explained. For deciding which tables need keys in the first place, see normalization. To query across these links, see MySQL joins explained. For what the primary key means for storage, see clustered vs non-clustered indexes.