Chapter 14 — Exercises

Build real structures. Use a scratch schema or a myproject database so you don't disturb Mercado (or wrap CREATE/DROP in BEGIN ... ROLLBACK). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — CREATE TABLE & types

14.1 Create a table wishlist (wishlist_id, customer_id, product_id, added_at) with appropriate types and an auto-generated primary key. (answer in Appendix)

14.2 Which type would you use for: a price, a product name, a yes/no flag, an order timestamp, a 1–5 rating? Justify each. (answer in Appendix)

14.3 Explain why numeric(10,2) and not real for money. Give a concrete failure of real.

14.4 ⭐ Why is text usually preferable to varchar(255) in PostgreSQL? When is a length limit actually justified?

Group B — Constraints

14.5 Add constraints to your wishlist table: customer_id and product_id are required and reference their parent tables; a customer can't wishlist the same product twice (composite UNIQUE or PK). (answer in Appendix)

14.6 Write a CHECK constraint ensuring a discount is >= 0 AND < 1.

14.7 Create a coupons table with a CHECK that expires_at > created_at.

14.8 ⭐ Add a named CHECK constraint to products ensuring price <= 100000, then test it by trying to insert a violating row. What error do you get (note the constraint name)?

Group C — Referential actions

14.9 Explain the difference between ON DELETE CASCADE, ON DELETE RESTRICT, and ON DELETE SET NULL. (answer in Appendix)

14.10 Why does Mercado cascade addresses on customer delete but restrict via orders? What would go wrong with CASCADE on orders?

14.11 ⭐ Design the FK actions for a comments table where deleting a post should delete its comments, but deleting a user should keep their comments (just null the author). Write the two FKs.

Group D — ALTER & DROP

14.12 Add a nullable notes text column to your wishlist, then set its default to ''. (answer in Appendix)

14.13 Explain why ALTER TABLE big_table ADD COLUMN status text NOT NULL; fails on a populated table, and give the safe 3-step pattern.

14.14 What's the difference between DELETE FROM t, TRUNCATE t, and DROP TABLE t?

14.15 ⭐ What does DROP TABLE products CASCADE do beyond dropping products? How would you find out what depends on it first?

Group E — Schemas

14.16 Create a schema reporting and a table inside it. Query it with a qualified name. (answer in Appendix)

14.17 ⭐ What does search_path control? How does it relate to the Chapter 2 "missing tables" mystery?

Group F — Build your schema (progressive project)

14.18 Write the complete CREATE TABLE statements for your project's core entities, with proper types and full constraints (PK, NOT NULL, UNIQUE, CHECK, FK + ON DELETE actions). (answer in Appendix — example provided for a library domain)

14.19 Insert a few valid rows, then deliberately try to violate each constraint (duplicate, bad value, dangling FK) and confirm the database rejects them.

14.20 ⭐ Save your DDL as schema.sql. Drop everything and re-run it to confirm it's idempotent/clean (hint: DROP ... IF EXISTS in dependency order, as Mercado's schema.sql does).

Self-check. If you can build a fully-constrained table from a set of requirements and explain every constraint and FK action, you're doing real database design — exactly what Part III formalizes next.