2.2 PRIMARY KEY, FOREIGN KEY, and JOIN

Relational databases are powerful not merely because they store many tables, but because relationships between those tables can stay clear, checkable, and queryable.

This section uses three tables throughout:

• students: student profiles.
• courses: course profiles.
• enrollments: enrollment records that connect students to courses.

PRIMARY KEY: give each row an identity first

A primary key uniquely identifies one row:

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

A primary key should be stable, unique, and non-null. Avoid using business fields that may change, such as display names.

students
+------------+------+
| student_id | name |
+------------+------+
| 1          | Ada  |
| 2          | Bo   |
+------------+------+
      ^
      |
   primary key

FOREIGN KEY: check parent rows before writing

A foreign key references another table's primary key. In the enrollment table, student_id must exist in students.student_id, and course_id must exist in courses.course_id.

students.student_id  <---  enrollments.student_id
courses.course_id    <---  enrollments.course_id

CREATE TABLE enrollments (
  student_id INTEGER,
  course_id INTEGER,
  enrolled_on DATE,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

If you insert student_id = 42 but there is no row 42 in students, the foreign-key check rejects that enrollment. This reduces orphaned data.

Product note: Foreign-key defaults, cascading delete syntax, and deferred checking differ by product. SQLite requires confirming PRAGMA foreign_keys = ON; MySQL requires attention to the storage engine; PostgreSQL has strong foreign-key support.

JOIN: build result rows from matches

Foreign keys answer "may this row point to existing rows when we write?" JOIN answers "how do we place related rows in one query result?" They often appear together, but they are not the same thing.

Start with one enrollment row:

enrollments row
+------------+-----------+-------------+
| student_id | course_id | enrolled_on |
+------------+-----------+-------------+
| 1          | 101       | 2026-01-12  |
+------------+-----------+-------------+
      |             |
      |             +--> courses.course_id = 101 -> SQL Basics
      +----------------> students.student_id = 1 -> Ada

First, connect the enrollment record to the student:

SELECT e.enrolled_on, s.name
FROM enrollments AS e
JOIN students AS s
  ON s.student_id = e.student_id;

The ON condition explains how the two tables match. The database does not simply guess from similar column names; you explicitly say that e.student_id must equal s.student_id.

Second, connect the same enrollment record to the course:

SELECT s.name, c.title, e.enrolled_on
FROM enrollments AS e
JOIN students AS s
  ON s.student_id = e.student_id
JOIN courses AS c
  ON c.course_id = e.course_id;

This query starts from the bridge table, enrollments. Each enrollment row finds one student, then one course, and finally outputs one "student + course + enrollment date" row.

students          enrollments              courses
Ada        <---   student_id=1
                  course_id=101     --->   SQL Basics

Bo         <---   student_id=2
                  course_id=102     --->   Data Modeling

INNER JOIN and LEFT JOIN

JOIN usually means INNER JOIN: keep only rows that match.

If you start from courses and want every course, including courses with no enrollment yet, use LEFT JOIN:

SELECT c.course_id, c.title, s.name, e.enrolled_on
FROM courses AS c
LEFT JOIN enrollments AS e
  ON e.course_id = c.course_id
LEFT JOIN students AS s
  ON s.student_id = e.student_id;

The result can look like this:

+-----------+---------------+------+
| course_id | title         | name |
+-----------+---------------+------+
| 101       | SQL Basics    | Ada  |
| 105       | Warehousing   | NULL |
+-----------+---------------+------+

NULL is not the string "NULL"; it means the right side had no matching row.

Common JOIN mistakes

• Forgetting the ON condition, causing row explosion.
• Joining on the wrong columns, such as connecting student_id to course_id.
• Assuming a foreign key automatically writes the JOIN for you; a foreign key is a constraint, while JOIN is a query.
• Misreading NULL in LEFT JOIN results.
• Filtering right-table columns in WHERE, accidentally making a LEFT JOIN behave more like an INNER JOIN.