Learn SQL: Keys
Keys connect tables
Yasmine opens the database. "Why does every player have a number instead of the name of their team? Nobody can read that." That number is exactly the clever part.
The primary key
Every table has an id column with a unique number per row: the primary key. Two teams can share a coach or play in the same category, but never have the same id.
That way you can point at any row without a doubt.
SELECT * FROM teams| id | name | category | coach_id |
|---|---|---|---|
| 1 | U11 | youth | 3 |
| 2 | U13 | youth | 1 |
| 3 | U15 | youth | NULL |
| 4 | Women A | seniors | 2 |
| 5 | Men A | seniors | 4 |
| 6 | Veterans | seniors | NULL |
The foreign key
In players you'll find team_id: the id of the team someone plays in. A column that points at the primary key of another table is called a foreign key.
Thanks to it, each team name is stored only once in the database. Rename a team and you change one row, not twenty.
SELECT name, team_id FROM players| name | team_id |
|---|---|
| Jonas Verhoeven | 5 |
| Wout Hermans | 5 |
| Bram De Smet | 5 |
| Yannick Diallo | 5 |
| Elke Van Dyck | 4 |
| Nora Aerts | 4 |
| Charlotte Maes | 4 |
| Ines Goossens | 4 |
SELECT id, name FROM teams| id | name |
|---|---|
| 1 | U11 |
| 2 | U13 |
| 3 | U15 |
| 4 | Women A |
| 5 | Men A |
| 6 | Veterans |
Try it yourself
Show the name and position of the U15 players. Look up the id first.
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson