Velamuvi AIAcademy

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
In this lesson you will learn
  • 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.

studentcountrycoursecategoryscore
Priya PatelIndiaPython for BeginnersProgramming97
Priya PatelIndiaSQL for BeginnersProgramming91
Priya PatelIndiaIntroduction to Machine LearningMachine Learning89

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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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.

Try it yourselfSQL
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

Practice exercise

Use the foreign key to find the enrollments of student 1. Show the course_id and progress of each.

Your solutionSQL
-- Student number 1 is Aarav Sharma.
-- Show the course_id and progress of each of his enrollments.
Knowledge check

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.