Schema & constraints
16 steps in 6 sets of SQL.
Designing the schema rather than querying it. Constraints, foreign keys and what ON DELETE actually does, triggers, views, and migrating a schema that already has data in it.
Sixteen steps. The constraints set is the important one: a rule enforced by the database holds even when the application forgets.
Constraints
- CHECK constraintsA constraint is a rule the database will not let your code forget.
- UNIQUE across columnsOne value unique on its own is a different rule from a pair unique together.
- Composite primary keysA join table's identity is the pair of things it joins, not a new number.
- STRICT tablesSQLite's flexible typing is a feature until the day you would rather it refused.
- Generated columnsA column whose value is a formula cannot disagree with the columns it reads.
Referential integrity
- ON DELETE and ON UPDATEThe foreign key says the reference must be real. The action says what happens when it stops being real.
- Turning integrity onSQLite only enforces foreign keys when you ask it to, once per connection.
Triggers
- A trigger that keeps a logA trigger is a statement the database runs for you on every write.
- A trigger that stamps a changeThe classic: keep an updated_at column honest without trusting every caller.
Views & scratch tables
- ViewsA view is a stored query you can select from like a table.
- Views on viewsA view is a query with a name, and naming the middle step is half of readable SQL.
- Temporary tablesA scratch table that disappears with the connection, which is exactly what a report needs.
Changing a schema
- ALTER TABLESchema changes after the fact: add, rename and drop columns.
- A migration, start to finishChanging a live table is four small statements, each of which can be re-run.
Encore
- schema_migration.sqlThe table rebuild: the only way to add a constraint SQLite cannot ALTER in.
- audit_trail.sqlThree triggers and a view: every change to a table, recorded where nobody has to remember to.