Two players called Sam
A gaming café keeps a list of its members. Two of them are called Sam.
One Sam plays for three hours. The other Sam gets the bill.
The café needs a way to tell every member apart, even when names match. A database does this with a key.
By the end of this lesson
Note
I can design a table with a primary key.
Key words
| Word | What it means | Example |
|---|---|---|
| Table | rows and columns about one kind of thing | |
| Record | one row: everything about one thing | |
| Field | one column: one fact about every record | first_name |
| Data type | the kind of value a field may hold | INT |
| Primary key | the field that is different in every record | id |
The cells students fill in are left blank in this preview.
A table holds one kind of thing
A database keeps its data in tables. The lab has an online shop with a table of customers and a table of products.
Each row is a record. Each column is a field. Every field has a data type, such as a whole number or some text.
Press the first two Runs to sign in. Then look at the customers table.
Let the terminal trust the server
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.
See the fields of the customers table
sql "DESCRIBE customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
A primary key tells records apart
Names repeat. A primary key is a field whose value is different in every record, and it is never empty.
In the customers table the key is id. The database counts it for you: 1, 2, 3 and so on.
This Run counts the records, the different first names and the different ids.
Count records, names and ids
sql "SELECT COUNT(*) AS records, COUNT(DISTINCT first_name) AS first_names, COUNT(DISTINCT id) AS ids FROM customers"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Worked example: which field can be the key?
| Field | Can it repeat? | Can it be empty? | Good key? |
|---|---|---|---|
| first_name | Yes, 40 records but 23 names | No | No |
| phone | Could be shared | Yes | No |
| city | Yes, many people per city | No | No |
| id | No, 40 records and 40 ids | No | Yes |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think a name makes a good key because most names look different. Names repeat, and people change them. Use a number the database counts, which never repeats and never changes.
Now design a table of your own
The lab lets you make any database whose name starts student_. You will make student_arcade for the café.
The players table has three fields: a counted id as the primary key, a gamertag and the day the player joined.
Run these in order. Two of them end with an error on purpose.
Make your own database
sql "CREATE DATABASE IF NOT EXISTS student_arcade"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 arcade='mariadb -h db -u student -pstudent student_arcade -t -e'Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Start clean
arcade "DROP TABLE IF EXISTS players"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Make the players table
arcade "CREATE TABLE players (id INT AUTO_INCREMENT PRIMARY KEY, gamertag VARCHAR(20) NOT NULL UNIQUE, joined_on DATE NOT NULL)"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Add two players
arcade "INSERT INTO players (gamertag, joined_on) VALUES ('NovaFox', '2026-09-01'), ('PixelPete', '2026-09-03')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Look at the table
arcade "SELECT * FROM players ORDER BY id"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Try to reuse id 1
arcade "INSERT INTO players (id, gamertag, joined_on) VALUES (1, 'LateJoiner', '2026-09-05')"Shown here, not run. Create a free account to press Run and type it into your own lab’s terminal.
Going further
Try to add a second player called NovaFox. The UNIQUE rule on gamertag refuses it.
Then try a player with no joined_on. Which rule refuses that one, and what does the error say?