Chapter 14 — Key Takeaways

The big idea

DDL (CREATE/ALTER/DROP) defines structure. CREATE TABLE turns the relational model (Ch. 3) into reality — and the types and constraints you choose are your data's contract. This is theme #1 at the keyboard: a good schema makes bad data impossible.

Types are correctness decisions

  • Money → numeric(p,s) (never real/double). Time → timestamptz. Text → text (not reflexive varchar(255)). Flags → boolean. Flexible → jsonb. (Appendix D.)
  • The wrong type causes money drift, date chaos, and silent bugs.

Constraints make integrity automatic

NOT NULL · DEFAULT · UNIQUE · PRIMARY KEY (one per table; can be composite) · CHECK · FOREIGN KEY. - Single-column rules → column constraints; multi-column rules → table constraints. - Name constraints for clear errors. - Referential actions: ON DELETE RESTRICT (default, blocks), CASCADE (deletes children — convenient but dangerous), SET NULL. Choose deliberately per relationship. - Every omitted constraint is a future bug — and constraints are hard to add once data is dirty. (Case Study 1.)

Auto-generated keys

id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY (prefer ALWAYS; serial is legacy). Backed by a sequence.

ALTER is powerful — and risky on live data

  • Adding a nullable, no-default column is fast (metadata-only).
  • Adding NOT NULL with no default to a populated table fails → safe pattern: add nullable/default → backfill (in batches) → SET NOT NULL.
  • ALTER takes an ACCESS EXCLUSIVE lock; row-rewriting/scanning alters hold it the whole time → can cause outages on hot tables. Use batches, low-traffic windows, lock_timeout, NOT VALID+VALIDATE, and CREATE INDEX CONCURRENTLY. (Case Study 2 → why migrations are a discipline, Ch. 22.)

DROP & schemas

  • Removal hierarchy: DELETE (some rows) → TRUNCATE (all rows) → DROP TABLE (the table). DROP ... CASCADE also removes dependents — check \d+ first.
  • A schema is a namespace (default public); search_path controls unqualified lookups (Ch. 2 mystery).

You can now…

  • ☐ Write CREATE TABLE with correct types and all constraint kinds.
  • ☐ Define foreign keys with the right ON DELETE actions.
  • ☐ Use GENERATED ... AS IDENTITY for keys.
  • ☐ ALTER safely (and recognize lock-heavy changes).
  • ☐ Use DROP, CASCADE, and schemas knowingly.
  • ☐ Build a fully-constrained schema for your project.

Looking ahead

Chapter 15 — Views, Materialized Views & Functions. Reusable SQL objects: views (name/hide complex queries, control access), materialized views (cache results), and user-defined functions/triggers (logic in the database).

One sentence to carry forward: The constraints and types you choose at CREATE TABLE are your data's permanent contract — declare them generously, because a good schema makes bad data unrepresentable.