Primary and foreign keys

Why it matters

So far every query has used one table. Real databases split data across many tables on purpose: customers in one, orders in another, products in a third. Keys are what hold that design together. Understanding them lets you read an unfamiliar schema, write correct joins, and avoid the duplicated, contradictory data that spreadsheets are famous for.

Why split data into tables at all?

Imagine storing everything in one wide orders table:

Ana's details are repeated in every order. When she moves, someone must update every row; miss one and the data contradicts itself (row 3 already has a typo and an old city). This is called an update anomaly.

The relational fix is to store each fact once:

customers

orders

Each order points at Ana through customer_id. Change her city once and every order sees it. Organising tables this way is called normalisation.

Primary keys

A primary key (PK) uniquely identifies each row of a table. Rules:

• Unique: no two rows share the value.
• Not null: every row has one.
• Stable: it should not change, because other tables refer to it.

Most tables use a surrogate key: a meaningless number generated by the database, such as id INTEGER PRIMARY KEY. The alternative is a natural key, a real-world value like an email or a product code. Natural keys are tempting but risky: emails change, product codes get reissued, and some "unique" values (names, phone numbers) are not unique at all. A common pattern is a surrogate id as the primary key plus a UNIQUE constraint on the natural value, so duplicates are still blocked.

A composite key uses two or more columns together. In an enrollments table, the pair (student_id, course_id) can be the key: a student enrolls in a course once, but appears many times overall.

Foreign keys

A foreign key (FK) is a column whose values must match a primary key in another table. orders.customer_id is a foreign key referencing customers.id.

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total       REAL NOT NULL
);

With the constraint switched on, the database refuses an order for customer 999 if no such customer exists. This is referential integrity: references always point at something real. It also controls deletes: by default you cannot delete a customer who still has orders, and you can choose ON DELETE CASCADE (delete their orders too) or ON DELETE SET NULL (keep the orders, clear the link). Choose carefully; cascading deletes can remove far more than you expect.

One SQLite detail: foreign key enforcement is off by default for backward compatibility, and is turned on per connection with PRAGMA foreign_keys = ON;. PostgreSQL, SQL Server and MySQL (InnoDB) enforce foreign keys automatically.

The three kinds of relationship

One-to-many is the most common. One customer has many orders; each order belongs to one customer. The foreign key goes on the "many" side (orders.customer_id).

One-to-one is rarer. One user has one profile. The foreign key column on one side is also UNIQUE, so each user can appear only once. It is used to split rarely used or sensitive columns into their own table.

Many-to-many: a student takes many courses and a course has many students. You cannot put a single foreign key on either side. Instead you add a junction table (also called a bridge or linking table):

enrollments

Each row links one student to one course, and the pair is the primary key. The junction table can also hold facts about the relationship itself, such as the enrolment date or a grade.

Reading a schema

When you meet a new database, find the keys first:

1. For each table, find the primary key. That tells you what one row represents ("one row per order").
2. Find columns ending in _id. They are usually foreign keys, and tell you how the tables connect.
3. Sketch the tables as boxes and draw a line for each foreign key. This is an entity-relationship diagram (ERD). Tools such as dbdiagram.io or your database client can draw one for you.

That sketch tells you which tables to join, and on which columns, before you write a line of SQL.

Common mistakes

• Using names as keys. Two customers can both be called Ana Silva.
• Changing primary key values. Every foreign key pointing at them breaks or must be updated too.
• Storing lists in one column, such as course_ids = '10,11,12'. It cannot be joined or constrained. Use a junction table.
• Skipping foreign key constraints. Without them, orphaned rows (orders for deleted customers) creep in.
• Assuming SQLite enforces foreign keys. Turn them on with the pragma.

Quick recap

• Store each fact once and link tables with keys.
• A primary key is unique, not null and stable; surrogate keys are the safe default.
• A foreign key references another table's primary key and enforces referential integrity.
• One-to-many puts the FK on the many side; many-to-many needs a junction table.
• Read a schema by finding the keys first.

Try it

This lesson has no code exercise. Open the Do tab and take the quiz, then use these ideas in the next lesson's join.