Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Primary Keys, Foreign Keys, and Relationships
This device
Course contentsPrimary Keys, Foreign Keys, and Relationships · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONData Types and NULL
NEXT LESSONYour First SELECT Query
Relational foundations · Lesson 04 130 min

Primary Keys, Foreign Keys, and Relationships

Keys turn separate tables into a connected model. A primary key says “this exact row.” A foreign key says “this row depends on that row.” Relationships let you store each fact once, protect references at write time, and rebuild useful results with joins when a question needs data from several tables.

What you will leave with

You will be able to choose stable primary keys, protect business identifiers with UNIQUE, model one-to-many and many-to-many relationships, name foreign-key roles clearly, prevent orphan rows, decide when a bridge table is needed, understand delete actions such as CASCADE, and write joins that follow relationships instead of duplicating facts.

Keys turn separate tables into a connected model

Primary keysGive every row a stable identity that other rows can reference safely.
Foreign keysStore a reference to a parent row and ask the database to protect that reference.
CardinalityDistinguish one-to-one, one-to-many, and many-to-many relationships before writing tables.
Referential integrityReject orphan rows and choose deliberate behavior when a parent row changes or disappears.

Read the relationship before you write SQL

Relational design is easier when you draw the sentence first. “One department has many courses” means the child table, courses, stores a department_id foreign key. The department name stays in departments. A query joins the tables when a report needs both the course title and department label.

PARENT TABLEdepartmentsdepartment_id PKname UNIQUE
1 to many
CHILD TABLEcoursescourse_id PKdepartment_id FK
ONE TO ONE

One profile per user

The child key can also be unique, so each parent has at most one matching child row.

ONE TO MANY

Many courses per department

The child table stores one foreign key column pointing to the parent table.

MANY TO MANY

Many students per course

A bridge table stores one row per pair, often with its own relationship attributes.

Primary keys identify rows, not display labels

A primary key should be unique, non-null, and stable. A person’s display name, a course title, or a department name may look unique today, but those labels can change. A stable internal identifier lets human-facing labels evolve without breaking every relationship that points at the row.

That does not mean business values are unimportant. A username, SKU, slug, or email can still be protected with UNIQUE. The difference is responsibility: the primary key carries identity; unique business keys protect domain rules and lookup convenience.

SQL BROWSER RUNNER

Keep identity stable while labels change

Update a member display name while the member_id and username continue to identify the same row.

CREATE TABLE members (
  member_id INTEGER PRIMARY KEY,
  username TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

INSERT INTO members (member_id, username, display_name) VALUES
  (1, 'ada', 'Ada Lovelace'),
  (2, 'grace', 'Grace Hopper');

UPDATE members
SET display_name = 'Amazing Grace'
WHERE member_id = 2;

SELECT member_id, username, display_name
FROM members
ORDER BY member_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Try inserting a duplicate username, then a duplicate member_id. Compare the two failures and explain what each rule protects.

Good identity key

Stable, required, unique

Usually a generated integer, UUID, or another value designed specifically to identify one row.

Risky identity key

Meaning changes over time

Emails, usernames, titles, and names are useful domain values, but they may need to change.

Foreign keys protect relationships at write time

A foreign key column stores a value that must match a candidate key in another table. In SQLite, foreign-key enforcement must be enabled with PRAGMA foreign_keys = ON. In production systems, enforcement behavior and defaults vary, but the design goal is the same: prevent a child row from pointing to nothing.

SQL BROWSER RUNNER

Prevent orphan rows

Create authors and articles, then optionally uncomment the invalid article insert to see the foreign key reject it.

PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  author_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE articles (
  article_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

INSERT INTO authors (author_id, name) VALUES
  (1, 'Mina'),
  (2, 'Omar');

INSERT INTO articles (article_id, title, author_id) VALUES
  (10, 'Readable SQL', 1),
  (11, 'Schema Habits', 2);

-- Uncomment this line to see the relationship rule fail:
-- INSERT INTO articles (article_id, title, author_id) VALUES (12, 'Orphan Article', 99);

SELECT articles.title, authors.name AS author
FROM articles
JOIN authors ON authors.author_id = articles.author_id
ORDER BY articles.article_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Uncomment the orphan insert with author_id 99. Then add author 99 first and rerun so the article becomes valid.

Foreign keys are not just documentation

A diagram helps humans, but a declared foreign key lets the DBMS reject bad writes. Without enforcement, an application bug, import script, or admin query can create orphan rows that later reports quietly misread.

Model one-to-many by placing the key on the many side

In a one-to-many relationship, each child row belongs to one parent row, while a parent can have many children. Put the foreign key on the child table. In the course example, each course belongs to one department, so courses.department_id references departments.department_id.

SQL BROWSER RUNNER

Join a one-to-many relationship

Create departments and courses, then join through department_id to produce a readable course catalog.

PRAGMA foreign_keys = ON;

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  department_id INTEGER NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, name) VALUES
  (1, 'Programming'),
  (2, 'Design');

INSERT INTO courses (course_id, title, department_id) VALUES
  (101, 'SQL Foundations', 1),
  (102, 'JavaScript DOM', 1),
  (201, 'UX Research', 2);

SELECT courses.title, departments.name AS department
FROM courses
JOIN departments
  ON departments.department_id = courses.department_id
ORDER BY departments.name, courses.title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add another Programming course and another department. Predict how the ORDER BY changes the final report.

Many-to-many relationships need a bridge table

If many students can take many courses, neither table should store a comma-separated list of the other table’s IDs. That creates unqueryable text, weak validation, and painful updates. A bridge table stores one row per relationship pair. It can also store facts about the relationship itself, such as enrolled_on, status, role, quantity, or sort order.

The bridge table below uses a composite primary key: (student_id, course_id). That means the same student cannot enroll in the same course twice. Each column is also a foreign key, so the pair must reference existing rows on both sides.

SQL BROWSER RUNNER

Build a student-course bridge table

Model many students taking many courses without storing lists inside a cell.

PRAGMA foreign_keys = ON;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL UNIQUE
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students (student_id, email) VALUES
  (1, 'ada@example.test'),
  (2, 'grace@example.test');

INSERT INTO courses (course_id, title) VALUES
  (101, 'SQL Foundations'),
  (102, 'Data Modeling');

INSERT INTO enrollments (student_id, course_id, enrolled_on) VALUES
  (1, 101, '2026-09-01'),
  (1, 102, '2026-09-02'),
  (2, 101, '2026-09-03');

SELECT students.email, courses.title, enrollments.enrolled_on
FROM enrollments
JOIN students ON students.student_id = enrollments.student_id
JOIN courses ON courses.course_id = enrollments.course_id
ORDER BY students.email, courses.title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Try inserting the same (student_id, course_id) pair again, then add a new course and enroll both students in it.

studentsstudent_id PKOne row per learner
enrollmentsstudent_id FKcourse_id FKOne row per student-course pair
coursescourse_id PKOne row per course

Use role names when one table references another more than once

A table can reference the same parent table through different roles. A code review has an author and a reviewer, and both are users. Naming the columns author_id and reviewer_id makes the relationship readable. The query then joins users twice using aliases, once for each role.

SQL BROWSER RUNNER

Join the same parent table twice

Use clear foreign-key role names and table aliases to show author and reviewer names in one result.

PRAGMA foreign_keys = ON;

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE code_reviews (
  review_id INTEGER PRIMARY KEY,
  pull_request_title TEXT NOT NULL,
  author_id INTEGER NOT NULL,
  reviewer_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES users(user_id),
  FOREIGN KEY (reviewer_id) REFERENCES users(user_id),
  CHECK (author_id <> reviewer_id)
);

INSERT INTO users (user_id, name) VALUES
  (1, 'Ada'),
  (2, 'Grace'),
  (3, 'Linus');

INSERT INTO code_reviews
  (review_id, pull_request_title, author_id, reviewer_id)
VALUES
  (100, 'Add SQL exercises', 1, 2),
  (101, 'Refine course navigation', 3, 1);

SELECT
  code_reviews.pull_request_title,
  author.name AS author,
  reviewer.name AS reviewer
FROM code_reviews
JOIN users AS author ON author.user_id = code_reviews.author_id
JOIN users AS reviewer ON reviewer.user_id = code_reviews.reviewer_id
ORDER BY code_reviews.review_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Try making author_id and reviewer_id the same value. Then explain which rule rejects self-review.

Choose delete actions deliberately

What should happen to child rows when a parent row is deleted? SQL engines support referential actions such as reject the delete, set the child foreign key to NULL, set a default value, or cascade the delete to child rows. There is no universally correct option. The right choice depends on whether the child row has meaning without the parent.

In the example, tasks are treated as dependent records of a project, so deleting a project cascades to its tasks. That can be reasonable for disposable child data. It would be dangerous for financial records, audit logs, or purchases where history must remain even if a user or project is archived.

SQL BROWSER RUNNER

Observe ON DELETE CASCADE

Delete one project and watch its dependent tasks disappear while unrelated tasks remain.

PRAGMA foreign_keys = ON;

CREATE TABLE projects (
  project_id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE project_tasks (
  task_id INTEGER PRIMARY KEY,
  project_id INTEGER NOT NULL,
  title TEXT NOT NULL,
  FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

INSERT INTO projects (project_id, name) VALUES
  (1, 'SQL course'),
  (2, 'Template marketplace');

INSERT INTO project_tasks (task_id, project_id, title) VALUES
  (10, 1, 'Write key lesson'),
  (11, 1, 'Add quiz'),
  (20, 2, 'Verify downloads');

DELETE FROM projects
WHERE project_id = 1;

SELECT project_tasks.task_id, project_tasks.title, projects.name AS project
FROM project_tasks
JOIN projects ON projects.project_id = project_tasks.project_id
ORDER BY project_tasks.task_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change ON DELETE CASCADE to ON DELETE RESTRICT, rerun, and observe how the attempted delete changes.

REFERENTIAL ACTION CHOICEWhat should happen when the parent changes?
  1. 01
    Reject

    Use when child records must keep a valid parent and deletion should stop.

    Example

    Do not delete a plan while active subscriptions reference it.

  2. 02
    Cascade

    Use when child records are truly owned by the parent and have no independent history.

    Example

    Delete draft checklist items when deleting the draft checklist.

  3. 03
    Set NULL

    Use only when the child relationship is optional and absence is a valid state.

    Example

    Keep a ticket after its optional assignee is removed.

  4. 04
    Archive instead

    Use application workflow when deleting would erase important history.

    Example

    Archive customers, invoices, and purchases instead of deleting them.

Common relationship mistakes

department_name copied into courses

Duplicated parent facts

Copying descriptive parent data into every child row creates update drift. Store the parent once and join when needed.

course_ids = "1,2,3"

Lists inside one cell

A comma-separated relationship cannot be protected by foreign keys and becomes hard to filter, count, and update.

user_id means many roles

Ambiguous foreign key role

Name the role: author_id, reviewer_id, owner_id, assignee_id, or manager_id.

delete first, think later

Unsafe referential actions

Cascade and restrict rules are business decisions. Choose them before production data depends on them.

Independent lab: model course enrollments

Extend the working enrollment model. Add a third student, add a third course, enroll the new student in two courses, mark one existing enrollment as completed, and return a report with student name, course title, status, and enrollment date. Then try to insert an enrollment for a nonexistent student and observe the foreign-key failure.

SQL BROWSER RUNNER

Build a protected enrollment model

Use two entity tables and one bridge table, then prove the model prevents duplicates and orphan relationships.

PRAGMA foreign_keys = ON;

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  full_name TEXT NOT NULL
);

CREATE TABLE courses (
  course_id INTEGER PRIMARY KEY,
  slug TEXT NOT NULL UNIQUE,
  title TEXT NOT NULL
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_id INTEGER NOT NULL,
  status TEXT NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'completed', 'dropped')),
  enrolled_on TEXT NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO students (student_id, email, full_name) VALUES
  (1, 'ada@example.test', 'Ada Lovelace'),
  (2, 'grace@example.test', 'Grace Hopper');

INSERT INTO courses (course_id, slug, title) VALUES
  (101, 'sql-foundations', 'SQL Foundations'),
  (102, 'data-modeling', 'Data Modeling');

INSERT INTO enrollments (student_id, course_id, status, enrolled_on) VALUES
  (1, 101, 'active', '2026-09-01'),
  (1, 102, 'completed', '2026-09-02'),
  (2, 101, 'active', '2026-09-03');

SELECT students.full_name, courses.title, enrollments.status
FROM enrollments
JOIN students ON students.student_id = enrollments.student_id
JOIN courses ON courses.course_id = enrollments.course_id
ORDER BY students.full_name, courses.title;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Add the requested student, course, enrollments, and completed status. Then test one duplicate pair and one nonexistent student_id.

Lab review criteria

Your final report should be produced by joins, not copied names inside enrollments. The bridge table should keep one row per student-course pair. Duplicate enrollment pairs should fail. References to missing students or courses should fail while valid new relationships succeed.

Lesson review

Primary keys give rows stable identity. Unique constraints protect business values that must not repeat. Foreign keys store and enforce references between rows. One-to-many relationships place the foreign key on the many side. Many-to-many relationships use a bridge table with foreign keys to both sides. Clear role names make repeated references understandable. Referential actions decide what happens when parent rows are updated or deleted, so they should be treated as part of the data model, not an afterthought.

  • I can explain why a primary key should be stable, unique, and non-null.
  • I can choose between a primary key and a unique business key.
  • I can place a foreign key on the correct side of a one-to-many relationship.
  • I can model a many-to-many relationship with a bridge table instead of a list inside one cell.
  • I can name foreign-key roles clearly when one table references the same parent more than once.
  • I can describe the trade-off between restrict, cascade, set null, and archive workflows.
KNOWLEDGE CHECK

Check your key and relationship model

Answer all eight questions, then use the explanations to repair any weak spot before you move into SELECT filtering.

01What is the core job of a primary key?
02Why is a changing display name usually a poor primary key?
03What does a foreign key protect?
04Which relationship does courses.department_id usually model?
05Why do many-to-many relationships usually need a bridge table?
06What does ON DELETE CASCADE do?
07What is an orphan row?
08What should a relationship name or foreign key column make clear?
PREVIOUS LESSONData Types and NULL
NEXT LESSONYour First SELECT Query
ON THIS PAGEPrimary Keys, Foreign Keys, and RelationshipsLesson mapRelationship mapPrimary keysForeign keysOne-to-manyMany-to-manyRole namesDelete actionsCommon mistakesIndependent labLesson reviewKnowledge check
Course contents