Skip to content

exercises.md

Latest commit

 

History

History
227 lines (138 loc) · 6.01 KB

exercises.md

File metadata and controls

227 lines (138 loc) · 6.01 KB

Section 6 — Relationships

What you'll practice: modelling data across multiple tables. You'll add PRIMARY KEY and FOREIGN KEY constraints, understand one-to-many relationships, see what happens when foreign keys are violated, and use ON DELETE to decide how referenced rows should behave when the parent row is removed.

Learning objectives

  • Add a PRIMARY KEY constraint with ALTER TABLE
  • Add a FOREIGN KEY constraint that REFERENCES another table
  • Understand the difference between 1-to-1, 1-to-many, and many-to-many
  • Insert rows that respect a foreign key
  • Trigger — and understand — foreign key violations
  • Use ON DELETE CASCADE, ON DELETE SET NULL, and ON DELETE RESTRICT

How to run these exercises

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/06-relationships/schema.sql

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/06-relationships/seed.sql
SET search_path TO sqlf_relationships;
SELECT COUNT(*) FROM person;  -- expect 20
SELECT COUNT(*) FROM car;     -- expect 25

The loaded schema.sql already includes the constraints. The exercises below walk you through building them up on throwaway tables inside the same schema so you see every step. Don't worry — your real person and car tables won't be touched.

Exercises

Exercise 1 — Describe the loaded tables

Run \d person and \d car in psql.

Question: in the car table, what does the line owner_id | integer | ... REFERENCES person(id) ON DELETE SET NULL tell you?

Exercise 2 — Build a throwaway author table with a primary key

Create a table called author_tmp with id INTEGER and name VARCHAR(100). Then add a primary key constraint on id using ALTER TABLE.

Hint:

CREATE TABLE author_tmp (id INTEGER, name VARCHAR(100) NOT NULL);
ALTER TABLE author_tmp ADD CONSTRAINT pk_author PRIMARY KEY (id);

Verify: \d author_tmp.

Exercise 3 — Insert two authors

Insert (1, 'Robert Martin') and (2, 'Martin Fowler') into author_tmp.

Exercise 4 — Try to insert a duplicate id

Insert (1, 'Duplicate Bob') into author_tmp.

Question: what error do you get? Which constraint enforced it?

Exercise 5 — Try to insert a NULL id

Insert (NULL, 'Anonymous') into author_tmp.

Question: why does this fail even though you never wrote NOT NULL on id?

Exercise 6 — Build a child book_tmp table

Create:

CREATE TABLE book_tmp (
    id        INTEGER PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INTEGER
);

Now add a foreign key on author_id that references author_tmp(id).

Hint:

ALTER TABLE book_tmp
ADD CONSTRAINT fk_book_author FOREIGN KEY (author_id) REFERENCES author_tmp(id);

Exercise 7 — Insert a book that references a real author

Insert (1, 'Clean Code', 1) into book_tmp.

Verify:

SELECT * FROM book_tmp WHERE id = 1;

Exercise 8 — Insert a book with an invalid author_id

Try to insert (2, 'Mystery Book', 999) into book_tmp.

Question: what error do you get? Why is it protecting you?

Exercise 9 — Delete an author that has a book

Try DELETE FROM author_tmp WHERE id = 1;.

Question: what error do you get, and why? This is ON DELETE behaviour being the default — NO ACTION.

Exercise 10 — Re-create book_tmp with ON DELETE CASCADE

Drop book_tmp, recreate it with the foreign key clause inline:

DROP TABLE book_tmp;
CREATE TABLE book_tmp (
    id        INTEGER PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES author_tmp(id) ON DELETE CASCADE
);
INSERT INTO book_tmp VALUES (1, 'Clean Code', 1), (2, 'Refactoring', 2);

Now DELETE FROM author_tmp WHERE id = 1;.

Verify: SELECT * FROM book_tmp; — "Clean Code" should be gone too. That's cascade.

Exercise 11 — Clean up the throwaway tables

DROP TABLE book_tmp;
DROP TABLE author_tmp;

Exercise 12 — Query the real dataset: cars with owners

Using the loaded person and car tables, list every car with its owner_id. Show car.id, brand, model, owner_id.

Hint: no joins needed — owner_id is already on the car row.

Exercise 13 — Find cars with NO owner

Return every car where owner_id IS NULL. These are unassigned stock.

Verify: the seed has 3 such cars.

Exercise 14 — How many cars does each owner_id own?

Group car by owner_id and return the count per owner, sorted by count descending.

Hint: GROUP BY owner_id.

Question: do unassigned cars (NULL owner) show up as a row? What does that row mean?

Exercise 15 — Try to assign a car to a non-existent person

Insert (99, 'Mystery', 'Car', 2023, 50000.00, 999) into car. What happens?

Exercise 16 — See ON DELETE SET NULL in action

The car.owner_id foreign key is declared ON DELETE SET NULL. Delete person id = 1 (Ahmed) and then check what happened to Ahmed's cars.

Safety: wrap this in a transaction so you can ROLLBACK and keep the seed intact:

BEGIN;
DELETE FROM person WHERE id = 1;
SELECT id, brand, model, owner_id FROM car WHERE id IN (1, 2, 25);
ROLLBACK;

Verify after the ROLLBACK: Ahmed's cars should be back to owner_id = 1.

Bonus challenges

Bonus 1 — Composite primary key

Drop and recreate book_tmp so the primary key is (id, author_id) instead of just id. When does a composite key actually make sense?

Bonus 2 — Self-referencing relationship

Add a column manager_id INTEGER REFERENCES person(id) to the person table (inside a transaction you'll ROLLBACK), then figure out how you'd query "everyone reporting directly to person 7."

Bonus 3 — Many-to-many

Sketch a schema for books and authors where a book can have multiple authors and an author can write multiple books. What extra table do you need, and what are its columns?

Cleanup

DROP SCHEMA sqlf_relationships CASCADE;