Lesson 10 of 17 · Combining Tables
Primary and Foreign Keys
Understand how database tables are connected. Learn what primary keys and foreign keys are, why data is split across tables and how relationships work.
- 8 min read
- Beginner
- Reviewed
- Why data is split across several tables
- What a primary key is
- What a foreign key is and how it links two tables
- The difference between one-to-many and many-to-many relationships
Why not one big table?
Imagine storing everything about the school in a single table: one row for each enrollment, including the student's details and the course's details.
| student | country | course | category | score |
|---|---|---|---|---|
| Priya Patel | India | Python for Beginners | Programming | 97 |
| Priya Patel | India | SQL for Beginners | Programming | 91 |
| Priya Patel | India | Introduction to Machine Learning | Machine Learning | 89 |
Priya's name and country are stored three times. That causes real problems:
- Wasted space. The same facts are repeated in every row.
- Mistakes. If Priya moves to another country, every one of her rows must be changed. Miss one, and the database contradicts itself.
- Lost information. A student who has not enrolled in anything cannot be stored at all, because there is no course to put in the row.
The solution is to store each kind of thing once, in its own table, and connect the tables. That is what "relational" means.
Primary keys: a unique label for every row
To connect tables, every row needs a reliable way to be identified. Names do not work: two students can share a name, and names change.
A primary key is a column whose value is unique for every row and never missing. Most tables use a simple number called id.
SELECT id, name, country FROM students ORDER BY id;
Student number 6 is Priya Patel, and no other student will ever have the number 6. Because the key is unique, asking for it returns exactly one row.
SELECT * FROM students WHERE id = 6;
Each of our three tables has its own id column as its primary key.
Foreign keys: pointing at another table
Now look at the enrollments table.
SELECT id, student_id, course_id, score FROM enrollments WHERE student_id = 6;
It does not store any names. The column student_id holds the primary key of a student, and course_id holds the primary key of a course.
A column that stores another table's primary key is called a foreign key. It is a pointer: "for the details, look up this row over there".
students enrollments courses
-------- ----------- -------
id <------------------ student_id
name course_id ----------------> id
country progress title
age score category
Following a key by hand
The rows above say that student 6 is enrolled in courses 1, 2 and 3. To find out what those are, look them up in the courses table.
SELECT id, title FROM courses WHERE id IN (1, 2, 3);
You have just connected two tables manually: read the foreign keys from one, then look them up in the other. It works, but it takes two queries and some copying. In the next lesson, JOIN does it in one step.
Kinds of relationship
One-to-many. One student can have many enrollments, but each enrollment belongs to exactly one student. This is the most common relationship. The foreign key always sits on the "many" side.
Many-to-many. One student can take many courses, and one course can have many students. Two tables cannot express that directly. The answer is a third table in the middle that holds a pair of foreign keys. That is exactly what enrollments is. It also gives a natural home to facts about the pairing itself, such as progress and score.
What keys protect you from
Stored once, a fact can be changed once.
UPDATE students SET country = 'Canada' WHERE id = 6; SELECT id, name, country FROM students WHERE id = 6;
One row changed, and every enrollment that points to student 6 now shows the new country automatically, because the country was never copied in the first place.
A database can also be told to enforce its foreign keys, refusing an enrollment for a student who does not exist. This protection is called referential integrity.
Common mistakes
Use the foreign key to find the enrollments of student 1. Show the course_id and progress of each.
-- Student number 1 is Aarav Sharma. -- Show the course_id and progress of each of his enrollments.
What is a foreign key?
Summary
- Splitting data into several tables avoids repetition and contradictions.
- A primary key identifies each row uniquely. It is usually a column called
id. - A foreign key holds the primary key of a row in another table.
- A one-to-many relationship puts the foreign key on the "many" side.
- A many-to-many relationship needs a table in the middle.
Next: combining tables in a single query.
Finished reading? Tick it off to track your progress.