Use the schema to enforce the contract
Tables and constraints encode assumptions that query authors otherwise have to guess. Data changes introduce another question: what must happen atomically, and what happens when a write conflicts or repeats?
Suggested route: Study DDL and constraints before DML and upserts. Compare a declared uniqueness guarantee with deduplicating a query result.
By the end: Choose a constraint from an invariant and explain safe insert/update/delete behavior under that invariant.
Decision reference
| Operation | Start with |
|---|---|
| Define or change structure | CREATE / ALTER TABLE |
| Enforce identity or relationships | PRIMARY KEY / UNIQUE / FOREIGN KEY |
| Enforce row rules | NOT NULL / CHECK |
| Change rows | INSERT / UPDATE / DELETE |
| Handle conflicts | ON CONFLICT / MERGE; engine-specific semantics |
In this chapter
- Keys, constraints & defaultsEncode data rules in the database and understand what each constraint guarantees.
- CREATE, ALTER, DROP & TRUNCATEDistinguish changing a table's structure from changing its rows.
- INSERT, UPDATE & DELETEChange exactly the intended rows and inspect the results.
- Upserts & MERGEDefine how incoming records interact with existing keys.
How to study
Use the suggested route above. For each lesson, explain the decision in your own words, test its example, and identify a situation where a different approach would be needed.