Skip to content

exercises.md

Latest commit

 

History

History
208 lines (126 loc) · 6.38 KB

exercises.md

File metadata and controls

208 lines (126 loc) · 6.38 KB

Section 06 — Foreign Keys

What you'll practice: modelling parent/child relationships with FOREIGN KEY, enforcing referential integrity, and controlling what happens to children when a parent goes away with ON DELETE CASCADE / SET NULL / RESTRICT.

Learning objectives

  • Declare a foreign key in CREATE TABLE and with ALTER TABLE
  • Watch what happens when you insert a child row that points to a parent that doesn't exist
  • Choose between ON DELETE CASCADE, ON DELETE SET NULL, and ON DELETE RESTRICT
  • Understand why "order" is quoted in SQL (it's a reserved word)
  • Drop a foreign key

Schema

Four tables:

  • customer — people who buy things
  • product — things that can be bought
  • order — a customer placing an order (note the quotes — order is a reserved word!)
  • order_item — line items on an order, each pointing at a product

Relationships:

customer (1) ──< order (N) ──< order_item (N) >── product (1)
  • order.customer_id → customer.id with ON DELETE CASCADE (delete the customer, their orders go too)
  • order_item.order_id → order.id with ON DELETE CASCADE
  • order_item.product_id → product.id with ON DELETE SET NULL (keep the historical line item even if the product is removed)

How to run these exercises

psql -U postgres -d sql_exercise -f 04-advanced-databases/06-foreign-keys/schema.sql
psql -U postgres -d sql_exercise -f 04-advanced-databases/06-foreign-keys/seed.sql
psql -U postgres -d sql_exercise

Inside psql:

SET search_path TO adv_fks;

Exercises

Exercise 1 — Inspect the schema

Use \d "order" and \d order_item to see the foreign key declarations on each table.

Question: what do the FOREIGN KEY lines at the bottom of the output look like? Which actions did Postgres pick up from the schema?

Exercise 2 — A valid insert

Insert a new order for customer_id = 2 (Alan Turing) with a total_cents of 12999. Don't specify an id — the SERIAL takes care of it.

Verify: SELECT id, customer_id, total_cents FROM "order" WHERE customer_id = 2;

Exercise 3 — Break the FK on purpose

Try to insert an order for customer_id = 9999.

Question: what error do you get, and which constraint name appears in the error message?

Exercise 4 — Break it at the grandchild level

Try to insert an order_item with order_id = 9999.

Question: same error pattern? Which constraint name this time?

Exercise 5 — List every order with its customer's name

Join "order" and customer and return columns order_id, customer_name, placed_at, total_cents. Order by placed_at ascending.

Exercise 6 — Full order report

Join "order", customer, order_item, and product and return:

| order_id | customer_name | product_name | quantity |

Order by order_id, then product_name.

Exercise 7 — Watch CASCADE in action (customer → orders)

Delete customer id = 3 (Grace Hopper). Before running the delete:

  1. Count how many "order" rows belong to Grace.
  2. Run the DELETE FROM customer WHERE id = 3;.
  3. Count her orders again.

Question: why did her orders disappear without you explicitly deleting them?

Exercise 8 — Watch CASCADE at the order level

Delete order_id = 1. Before:

SELECT COUNT(*) FROM order_item WHERE order_id = 1;

After:

DELETE FROM "order" WHERE id = 1;
SELECT COUNT(*) FROM order_item WHERE order_id = 1;

Question: why was the behaviour the same as Exercise 7?

Exercise 9 — Watch SET NULL on product deletion

Delete product_id = 4 (Ergonomic Mouse).

SELECT id, order_id, product_id, quantity FROM order_item;

Question: what did the order_item.product_id become for rows that used to reference product 4? Why is SET NULL sometimes the right choice for historical line items, instead of CASCADE?

Exercise 10 — Try to delete a customer who has orders (RESTRICT-style)

The schema currently uses CASCADE. Let's switch to RESTRICT and watch the difference.

  1. Drop the existing FK on "order".customer_id: 
    ALTER TABLE "order" DROP CONSTRAINT order_customer_fk;
  2. Re-add it with ON DELETE RESTRICT: 
    ALTER TABLE "order"
  ADD CONSTRAINT order_customer_fk
  FOREIGN KEY (customer_id)
  REFERENCES customer (id)
  ON DELETE RESTRICT;
  3. Try to delete customer id 1 (Ada).

Question: what error do you get? When would RESTRICT be the right choice instead of CASCADE?

Exercise 11 — Add an FK with ALTER TABLE

Create a brand new table review with columns id SERIAL PK, product_id INT NOT NULL, rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5), body TEXT NOT NULL. Do not declare the foreign key in the CREATE TABLE.

Then, using ALTER TABLE, add a foreign key named review_product_fk from review.product_id to product.id with ON DELETE CASCADE.

Verify: \d review.

Exercise 12 — Orphan check with LEFT JOIN

Write a query that finds every order_item whose product_id is NULL (orphaned from Exercise 9). Show id, order_id, product_id, quantity.

Exercise 13 — Count orders per customer

Return customer_name, order_count, sorted by order_count DESC. Include customers with zero orders using a LEFT JOIN.

Exercise 14 — Drop a foreign key

Drop order_item_product_fk from order_item. Then show \d order_item to confirm it's gone.

Bonus challenges

Bonus 1 — Self-referencing FK

Add a manager_id INTEGER REFERENCES customer(id) column to customer. This lets a customer "manage" another customer (silly for an e-commerce model, but it's the classic example of a self-referencing FK). Insert one row, set another row's manager_id to that row, and write a query that joins customer to itself to return (employee_name, manager_name) pairs.

Bonus 2 — Deferrable constraints

Read the Postgres docs on DEFERRABLE INITIALLY DEFERRED. When is it useful? Rewrite the order_customer_fk so its check only runs at COMMIT time instead of on every statement.

Bonus 3 — Composite foreign key

Could you replace order_item.order_id with a composite key (order_id, customer_id) that references both "order".id and "order".customer_id together? Why would you want to?

Cleanup

DROP SCHEMA IF EXISTS adv_fks CASCADE;