Exercises

Objectives

By the end of this lesson, you should be able to:

• Design and build one-to-many, one-to-one, and many-to-many relationships
• Test that foreign keys and composite primary keys actually enforce what they’re supposed to
• Predict and confirm the effect of an ON DELETE action

⚠️ A note on verification: every query and result in this lesson was run against a real, live PostgreSQL 18 database.

Exercise 1: One-to-Many (Authors and Books)

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Jordan Ashworth'), ('Priya Kendall');
INSERT INTO books (title, author_id) VALUES
  ('The Long Harbor', 1),
  ('Silent Ridge', 1),
  ('Autumn Ledger', 2);

a) Write a join query (preview of Module 10) listing each book’s title alongside its author’s name. Expected result: Jordan Ashworth with both “The Long Harbor” and “Silent Ridge”, Priya Kendall with “Autumn Ledger”.

b) Try inserting a book with author_id = 999, and record the exact error PostgreSQL raises.

c) Write a query showing each author’s name and how many books they’ve written. Expected result: Jordan Ashworth 2, Priya Kendall 1.

Exercise 2: One-to-One (Users and Settings)

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE user_settings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
    theme VARCHAR(20) NOT NULL DEFAULT 'light'
);

INSERT INTO users (username) VALUES ('erin_c'), ('jordan_b');
INSERT INTO user_settings (user_id, theme) VALUES (1, 'dark');

a) Write a join query showing username and theme for users who have settings configured. Expected result: erin_c with theme dark, jordan_b doesn’t appear (no settings row yet).

b) Try inserting a second user_settings row for user_id = 1, and record the exact error.

c) Explain, in your own words, which constraint specifically caused the error in (b), and what would happen without it.

Exercise 3: Many-to-Many (Books and Genres)

CREATE TABLE genres (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE book_genres (
    book_id INTEGER REFERENCES books(id),
    genre_id INTEGER REFERENCES genres(id),
    PRIMARY KEY (book_id, genre_id)
);

INSERT INTO genres (name) VALUES ('Mystery'), ('Drama');
INSERT INTO book_genres (book_id, genre_id) VALUES (1, 1), (1, 2), (2, 1);

a) Write a join query listing each book’s title alongside every genre it belongs to. Expected result: “The Long Harbor” appears twice (Drama and Mystery), “Silent Ridge” appears once (Mystery).

b) Try inserting the pairing (1, 1) again (book 1, genre 1, already present), and record the exact error.

c) Explain, in your own words, why book_genres needs a composite primary key on both columns together, rather than a single-column primary key on just book_id or just genre_id.

Exercise 4: ON DELETE CASCADE

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders_del (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
    total NUMERIC(10,2) NOT NULL
);

INSERT INTO customers (name) VALUES ('Riley Nguyen');
INSERT INTO orders_del (customer_id, total) VALUES (1, 120.00), (1, 45.00);

a) Write a query confirming both orders exist before any delete happens. Expected result: 2 rows, totals 120.00 and 45.00.

b) Delete the customers row for Riley Nguyen, then re-query orders_del. Expected result: 0 rows, both orders were removed automatically by ON DELETE CASCADE.

c) Rebuild the same two tables, but with ON DELETE RESTRICT instead of CASCADE, insert the same data, and try the same delete. Describe what happens differently, and why that difference matters for choosing between the two options in a real system.

Recap

This module covered every core relationship-modeling tool: one-to-many with a plain foreign key, one-to-one by adding UNIQUE to that foreign key, many-to-many with a junction table and composite primary key, and ON DELETE actions controlling what happens to related rows when a parent row disappears. Together, these are how a real, multi-table database stays consistent as data changes.

Next module: JOIN, actually querying across these related tables at once, in full, going beyond the preview joins used in this module’s examples.