Tables are where data lives, but a well-designed table is far more than a list of columns — it is a set of guarantees. Constraints let the database itself enforce the rules your data must obey: that a value is present, unique, within range, or references a real row in another table. Encoding these rules in the schema means they hold no matter which application, script, or person writes the data.
Relationships connect tables into a model of the real world. A foreign key declares that one table's column refers to another's key, and PostgreSQL guarantees that reference stays valid. From these primitives you build the one-to-many and many-to-many structures that represent how entities actually relate, with the database refusing to let the data drift into an inconsistent state.
This lesson covers primary and foreign keys, the constraint family (NOT NULL, UNIQUE, CHECK, defaults), and how to model relationships correctly. The recurring principle is to make invalid states impossible at the schema level rather than merely discouraged in code, because the database is the one place every write must pass through.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.