Two jobs, one customer number
A repair shop keeps its jobs in a spreadsheet. Two different customers were given the number 31.
Now the receipt for one customer shows the other customer's phone.
A database stops this kind of mistake. It refuses a second number 31 before the damage is done.
By the end of this lesson
Note
I can make tables with keys in a relational database.
Key words
| Word | What it means | Example |
|---|---|---|
| Table | rows and columns that hold one kind of thing | |
| Data type | the kind of value a column may hold | INT |
| Primary key | the column that names each row, once only | id |
| Foreign key | a column that points at a row in another table | route_id |
| Constraint | a rule the database checks on every change | NOT NULL |
| NULL | the mark for a missing value |
The cells students fill in are left blank in this preview.
A table holds one kind of thing
A relational database keeps its data in tables. One table holds one kind of thing: customers, or products, or orders.
Each column has a data type. The database refuses text in a number column.
First tell the terminal how to sign in. These two Runs are the setup for the whole lesson.
Accept the server's certificate
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfShown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for the shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Look at one table's columns
sql "DESCRIBE reviews"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: choosing a type
| Fact to store | Type | Why |
|---|---|---|
| A product count | INT | Whole numbers only |
| A price in pounds | DECIMAL(8,2) | Exact pence, never a rounding error |
| A town name | VARCHAR(40) | Text of up to 40 characters |
| A day of dispatch | DATE | The database can work out days between |
| Is it discontinued | BOOLEAN | Only yes or no |
The cells students fill in are left blank in this preview.
A primary key names each row
A primary key is a column whose value is different in every row. It can never be NULL.
It is usually a number the database counts for you, AUTO_INCREMENT. The database refuses a second row with the same key.
A key can use two columns together. Then the pair must be different.
A key made of two columns
sql "DESCRIBE order_items"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A common mistake
Warning
Many people think a name or an email makes a good primary key. Names repeat, and people change their email. Use a number the database counts. Keep the email as a UNIQUE column beside it.
A foreign key points at another table
A parcel is sent along a route. The parcels table does not copy the route's details. It holds the route's number.
That column is a foreign key. The database checks that the number exists in the routes table.
So you cannot send a parcel along a route that is not there.
Now build two tables of your own
The lab lets you make any database whose name starts student_. You will make student_depot for a small courier.
The shop database stays as it was. Run these in order. A Run that ends with an error is part of the lesson.
Make your own database
sql "CREATE DATABASE IF NOT EXISTS student_depot"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make a short name for it
alias depot='mariadb -h db -u student -pstudent student_depot -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start clean: drop both tables
depot "DROP TABLE IF EXISTS parcels, routes"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the routes table
depot "CREATE TABLE routes (id INT AUTO_INCREMENT PRIMARY KEY, town VARCHAR(40) NOT NULL UNIQUE, days INT NOT NULL DEFAULT 1)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the parcels table
depot "CREATE TABLE parcels (id INT AUTO_INCREMENT PRIMARY KEY, route_id INT NOT NULL, weight_kg DECIMAL(5,2) NOT NULL, sent_on DATE NOT NULL, FOREIGN KEY (route_id) REFERENCES routes(id))"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Add two routes
depot "INSERT INTO routes (town, days) VALUES ('Leeds', 1), ('Bristol', 2)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Send a parcel along route 9
depot "INSERT INTO parcels (route_id, weight_kg, sent_on) VALUES (9, 2.5, '2026-03-02')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Add Leeds a second time
depot "INSERT INTO routes (town) VALUES ('Leeds')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try to drop the routes table
depot "DROP TABLE routes"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
Give the courier drivers. Make a drivers table in student_depot with a numbered primary key, a name and a UNIQUE licence number.
Then add a driver_id column to parcels with ALTER TABLE, and a foreign key from it to drivers. Prove each rule works with one row that breaks it.