⌘CodeQuest
Databases & SQL

Lesson 2 of 7

Lesson 2 — Tables, Keys and Relationships

Primary keys, foreign keys, and the one-to-many and many-to-many patterns behind every app.

🟢 Beginner 75 XP

Learn it

A primary key uniquely identifies a row — usually an id column.

A foreign key is a column that points at another table's primary key. That is how tables link.

One-to-many: one teacher has many students. Many-to-many: students take many courses and courses have many students.

Many-to-many needs a third 'join' table (also called a link or bridge table).

Key terms

Primary key
Column(s) that uniquely identify a row; never NULL.
Foreign key
A column referencing another table's primary key.
Referential integrity
The rule that foreign keys must point at rows that exist.
Join table
A bridge table that resolves a many-to-many relationship.
ERD
Entity relationship diagram — a map of your tables and links.

Model a school timetable

  1. 1Entities: student, course, teacher, room, lesson_slot.
  2. 2One-to-many: One teacher teaches many courses → course.teacher_id.
  3. 3Many-to-many: Students ↔ courses needs enrolment(student_id, course_id).
  4. 4Composite key: PRIMARY KEY (student_id, course_id) blocks double enrolment.
  5. 5Delete rule: Deleting a course should CASCADE its enrolments but RESTRICT if grades exist.

A many-to-many enrolment

sqlCREATE TABLE course (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE enrolment (
  student_id INTEGER REFERENCES student(id) ON DELETE CASCADE,
  course_id  INTEGER REFERENCES course(id)  ON DELETE RESTRICT,
  enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
  PRIMARY KEY (student_id, course_id)
);

The composite primary key means a student can appear once per course. Deleting a student cleans up their enrolments; deleting a busy course is blocked.

Try it

Match each term to its meaning.

Primary key
Foreign key
Join table
ON DELETE CASCADE
Composite key

Challenge

Design the tables for a school library: books (with multiple copies), members, loans and reservations. Give each table its key columns and say which relationships are one-to-many or many-to-many.

Pick whichever way suits you — every mode earns the same bonus XP.

Write at least 40 more characters to submit.

Mark your own work

Guided walkthrough — 0/3 clues revealed

  1. Clue 1 locked — reveal it only if you get stuck.
  2. Clue 2 locked — reveal it only if you get stuck.
  3. Clue 3 locked — reveal it only if you get stuck.

Each clue costs 4 XP (never below 19 XP). You'd earn 38 XP right now.

Quiz time

Question 1 of 4Score 0

How do you model a many-to-many relationship?