Primary and Foreign Keys

Imagine your school keeps a database of every pupil. There are three pupils called Jack Smith, two born on the same day, and a couple who share a form group. If a teacher asks the database to "pull up Jack Smith's record", which one does it fetch? A database can only be trusted if it can point at exactly one record with no confusion — and that is the job of a primary key.

Real databases also split their information across several tables — one for pupils, one for classes, one for exam results — to avoid repeating the same facts over and over. The glue that links those tables together is a foreign key. These two kinds of key are the backbone of every relational database, and they are a favourite topic in GCSE Computer Science.

The primary key: one field that identifies each record

A primary key is a field (a column) whose value is different for every single record in the table. Because no two records can share it, giving the database a primary-key value is enough to pin down one — and only one — record.

Here is a small Pupils table. The PupilID column is the primary key:

Pupils
PupilID primary key	Name	Year	ClassID
1024	Ava Khan	10	C3
1025	Ben Otto	10	C1
1026	Cara Diaz	10	C3
1027	Ava Khan	11	C1

Notice that there are two pupils called Ava Khan — so the Name column could never be the primary key. But PupilID is guaranteed unique: ask for PupilID 1024 and you get precisely one row.

A primary key has two firm rules:

Unique — no two records may share the same value.
Never empty — every record must have a value (you can't identify a record with a blank).

It is tempting to use a pupil's Name as the primary key — but a primary key must be unique and must never be blank, and a name is neither guaranteed. Two people really are called Ava Khan; a new pupil might be enrolled before their name is typed in (blank); and someone might change their name. A meaningless ID number sidesteps all of this: it is invented purely to be unique, it is always filled in, and it never changes. That is why databases lean on PupilID, OrderID, ISBN and the like instead of real-world names.

Watch out for one more thing: a foreign key references another table's primary key — it does not copy all of that table's data. The Pupils table stores the tiny ClassID value C3, not the class name, teacher and room. Store each fact once, then point to it.

The foreign key: a link between two tables

Where would you store which class each pupil is in? You could cram the class name, teacher and room into the Pupils table — but then you'd repeat "7 Gamma, Ms Lee, Room 14" on every pupil in that class, and one typo would break it. Instead we keep a separate Classes table, and the Pupils table simply points at it.

A foreign key is a field in one table that holds the primary key of another table. In the Pupils table, ClassID is a foreign key: each value (like C3) is the primary key of a row in the Classes table. That shared value is the link — the relationship — between the two tables.

Follow the arrow: ClassID in Pupils (the foreign key) matches ClassID in Classes (its primary key). The same field can be a primary key in its own table and a foreign key when it appears in another table.

Following a key in code

"Looking up" a foreign key just means finding the row in the other table whose primary key matches. This little program stores the two tables and, given a PupilID, follows ClassID across to fetch the class name. Change lookFor and press Run:

// Pupils: ClassID is a FOREIGN KEY pointing at Classes.ClassID const pupils = [ { PupilID: 1024, Name: "Ava Khan", ClassID: "C3" }, { PupilID: 1025, Name: "Ben Otto", ClassID: "C1" }, { PupilID: 1026, Name: "Cara Diaz", ClassID: "C3" }, ]; // Classes: ClassID is the PRIMARY KEY of this table const classes = [ { ClassID: "C1", ClassName: "7 Alpha" }, { ClassID: "C3", ClassName: "7 Gamma" }, ]; const lookFor: number = 1026; // try 1024, 1025 or 1026 const pupil = pupils.find(p => p.PupilID === lookFor); const klass = classes.find(c => c.ClassID === pupil.ClassID); // follow the foreign key console.log(pupil.Name + " is in class " + klass.ClassName);

The database never copied "7 Gamma" into the Pupils table — it stored only the key C3 and looked the rest up on demand. That is the whole point of a foreign key.

Yes — and it happens constantly. ClassID is the primary key of the Classes table (unique, identifies each class) and, over in the Pupils table, the very same field is a foreign key (it references Classes). A field's role depends on which table you're looking at. In a chain of tables — Pupils → Classes → Teachers — a middle table can hold a foreign key pointing further along while its own primary key is referenced from behind.