Primary Keys and Foreign Keys

How tables tell rows apart, and how two tables hold hands

Big Idea: A primary key is the column that gives every row its own name-tag, so no two rows can ever be mixed up. A foreign key is a column in one table that points at the primary key of another table, letting two tables link together instead of copying the same facts over and over.

By the end you will be able to:

explain what a primary key is and why every row needs one,
let the database hand out ID numbers automatically with AUTO_INCREMENT,
explain what a foreign key promises,
link two tables together and read data across them,
say why one shared key beats copying the same facts into many rows.

1. Why every row needs a name-tag

Imagine a classroom where three children are all called Alex. If the teacher shouts "Alex, it is your turn!", who stands up? Nobody knows. People need something that is truly their own so we can point at exactly one of them.

Database tables have the same problem. Two rows can look almost identical — same name, same age, same class. If we ever need to say "change this one row" or "delete that one row", we need a value that belongs to one row and one row only.

That special column is called the primary key. A primary key follows two simple promises:

Unique: no two rows may share the same value. Every value appears once.
Not empty (NOT NULL): every row must have one — a name-tag cannot be blank.

Because of these two promises, a primary key can always point to exactly one row, like a seat number in a cinema or the page number in a book.

2. Our example: a tiny school

We will use one small school database for the whole lesson. It has two tables: classes (the groups children belong to) and students (the children themselves). First we build the classes table and give it a primary key.

CREATE TABLE classes (
    class_id   INT PRIMARY KEY,
    class_name VARCHAR(50)
);

INSERT INTO classes (class_id, class_name) VALUES
(1, 'Sunflowers'),
(2, 'Maple Leaves');

The words PRIMARY KEY after class_id tell MySQL: "This column is the name-tag. Keep every value unique and never let it be empty." Here is what lives inside classes now:

class_id	class_name
1	Sunflowers
2	Maple Leaves

If we now tried to add another class with class_id = 1, MySQL would refuse with a duplicate key error — that is the primary key doing its job.

Primary Keys and Foreign Keys