Keys and relationships

Why is shop data split across several tables?

Picture the bike shop keeping everything in one big sheet: every repair on its own line, with the customer's name and phone number typed out each time. Owen Price has two repairs, so his phone number appears twice. When he changes number, someone has to find and fix both lines. Miss one and the shop now holds two different phone numbers for the same person, with no way to tell which is right.

Relational databases avoid this by storing each kind of thing once, in its own table. Customers go in customers, repairs go in repairs, parts go in parts. Each repair then says which customer it belongs to by quoting that customer's id. Owen's phone number lives in exactly one row, so one change fixes it everywhere.

The cost is that a question such as "which bikes did Maya Lin bring in?" now needs data from two tables. The keys are what make that possible. They are the threads that tie a row in one table to the right row in another.

What is a primary key?

A primary key is the column (or group of columns) that identifies one row and only one row. In this database every main table has an id INTEGER PRIMARY KEY column. No two customers can share an id, and the id never needs to change, even if the customer's name or phone does.

Names make poor keys. Look at the customers table: Maya Lin and Maya Lind differ by a single letter, and a shop of any size will soon have two people with exactly the same name. An id is short, unique and has no meaning of its own, which is exactly why it is safe to point at.

In SQLite, a column declared INTEGER PRIMARY KEY fills itself in with the next number when you add a row without one. PostgreSQL uses GENERATED ALWAYS AS IDENTITY (or the older SERIAL) for the same job, and MySQL uses AUTO_INCREMENT. You will add rows yourself in a later lesson.

SELECT id, name, phone
FROM customers
ORDER BY id;

What is a foreign key?

A foreign key is a column whose values are primary keys from another table. In repairs, the column customer_id holds a customer's id. Repair 2 has customer_id 3, which means "this repair belongs to the customer whose id is 3", and that is Maya Lin.

The schema says so explicitly: customer_id INTEGER NOT NULL REFERENCES customers(id). The REFERENCES part tells the database to refuse a repair that points at a customer who does not exist. Try to add a repair for customer 99 and you get an error mentioning a FOREIGN KEY constraint. That protection keeps orphan rows out of the data.

One SQLite detail: it only enforces foreign keys after PRAGMA foreign_keys = ON runs on the connection. This course turns it on for you. In your own projects with SQLite, add that line when you open the database, or the REFERENCES clauses are recorded but not checked. PostgreSQL and MySQL (with InnoDB tables) always enforce them.

SELECT id, customer_id, bike
FROM repairs
ORDER BY customer_id;

What does a one-to-many relationship look like?

One customer can have many repairs, but each repair belongs to exactly one customer. That shape is called one-to-many, and it is by far the most common relationship you will meet. Customers and orders, members and loans, members and sightings all work the same way.

The rule for building it is simple to state: the foreign key goes on the "many" side. Each repair row carries one customer_id. The customers table has no list of repair ids, because a single column cannot hold a list, and it does not need one: the repairs already say who they belong to.

Some customers have no repairs at all. Sam Doyle is in customers, but no repair points at id 4. That is normal and allowed. It will matter in the lesson on keeping unmatched rows, where you learn to list customers whether or not they have any repairs.

SELECT customer_id, COUNT(*) AS repairs
FROM repairs
GROUP BY customer_id;

How does a many-to-many relationship work?

A repair can use several parts, and the same part (an inner tube, say) turns up in many repairs. Neither table can hold a single foreign key for that, because both sides are "many". The answer is a third table that sits between them, called a linking table (you will also hear junction table or join table).

Here it is repair_parts. Each row records one pairing: repair 6 used part 1, two of them. It has two foreign keys, repair_id and part_id, and it can carry facts that belong to the pairing itself, such as qty. Its primary key is the pair (repair_id, part_id), which stops the same part being listed twice for the same repair.

Read a many-to-many as two one-to-many relationships pointing inwards: one repair has many repair_parts rows, and one part has many repair_parts rows. Once you see that, every linking table in every schema looks familiar.

SELECT repair_id, part_id, qty
FROM repair_parts
WHERE repair_id = 6;

How do you read a schema?

Open the schema tab next to query.sql. It shows the CREATE TABLE statements that built this database. Writing these comes later. Reading them now tells you how the tables connect before you write a single query.

For each table, scan for three things. First, the primary key: the column marked PRIMARY KEY, or a PRIMARY KEY (a, b) line at the end. Second, the foreign keys: any column with REFERENCES other_table(id). Third, the naming pattern: a column called customer_id almost always holds an id from customers, even in databases where nobody wrote REFERENCES.

Then sketch it, even roughly, on paper. Draw a box per table and an arrow from each foreign key to the table it points at. For this shop you get repairs pointing at customers, and repair_parts pointing at both repairs and parts. A table with two arrows leaving it is usually a linking table.

• customers: primary key id, no foreign keys.
• repairs: primary key id, foreign key customer_id to customers.
• parts: primary key id, no foreign keys.
• repair_parts: primary key (repair_id, part_id), foreign keys to repairs and parts.

How do you follow an id from one table to another?

Before learning JOIN, you can already answer cross-table questions in two steps. Look up the id in the first table, then use it as a filter in the second. That is what the task asks: find Maya Lin's id, then list repairs WHERE customer_id = 3.

Doing it by hand once is worth the effort, because it is exactly what a join does for every row at once. The next lesson replaces the copying of the number with an ON condition that matches repairs.customer_id to customers.id automatically.

You can follow ids in the other direction too. Repair 7 used parts 4 and 5 according to repair_parts. Look those up in parts with WHERE id IN (4, 5) and you have the gear cable and the tyre. Use the Tables view to see all four tables side by side while you trace a few ids by eye.

SELECT id, name, price_pence
FROM parts
WHERE id IN (4, 5);

What should you check when AI-written SQL uses keys?

AI tools usually guess column names from the question. When a query uses keys, check these points against the real schema before trusting the answer.

• The filter uses the id column, not a name. WHERE name LIKE 'Maya%' would happily include Maya Lind.
• The foreign key is matched to the right table's primary key. repair_parts.part_id belongs with parts.id, never with repairs.id.
• The column names exist. Generated SQL often invents customerId or client_id when the schema says customer_id.
• Linking tables are used. A many-to-many question answered without the linking table is answering something else.

Field notes

• Splitting data into tables means each fact, like a phone number, is stored once.
• A primary key is a column that identifies exactly one row, such as id.
• A foreign key holds the id of a row in another table, like customer_id.
• In one-to-many, the foreign key goes in the table on the "many" side.

Easy to get wrong

customer_id stores the customer's number, never their name. Comparing it with text matches nothing.

A linking table has two foreign keys. Use the one that points at the table your number came from.

Names are not unique and can nearly match. Once you know the id, filter on the id.

Questions people ask

Do I have to call the key column id?

No, any name works. id for the primary key and table_id for foreign keys is a widespread habit, and it makes schemas much easier to read.

Can a foreign key be NULL?

Yes, unless the column says NOT NULL. A NULL foreign key means "not linked to anything", such as a walk-in sale with no known customer.

Can a primary key be made of two columns?

Yes. That is a composite key, as in repair_parts. The pair must be unique, while each column on its own may repeat.

What happens if I delete a customer who still has repairs?

With foreign keys enforced, the delete fails with a FOREIGN KEY constraint error, which protects the repairs from pointing at nobody. The lesson on rules for your data covers other options.