Quiz
Warm-up from lesson 7-3: in SQLite, what does INTEGER PRIMARY KEY give you beyond uniqueness?
Types: what a column may hold
Designing a schema (the set of tables, columns, and rules) starts with picking a type for each column. SQLite's everyday types:
| Type | Holds | Examples |
|---|---|---|
INTEGER | whole numbers | ids, counts, ages |
REAL | decimal numbers | ratings, weights |
TEXT | strings | names, emails |
BLOB | raw bytes | images (rarely used directly) |
Dates are usually stored as TEXT in 'YYYY-MM-DD' form, which sorts correctly (year first, then month, then day, so alphabetical order IS chronological order). Boolean values, meaning true/false answers like "is this account active?", are stored as INTEGER 1 for true and 0 for false, because SQLite has no separate true/false type.
Why types matter: stored as the wrong type, data misbehaves. The classic is numbers stored as text, where sorting goes alphabetical: as text, '9' is greater than '10' because the character 9 comes after 1. The block below proves it, and shows typeof(), which reports what a value actually is.
Code exercise · sql
Run it. Line 1 shows the type of each literal. Then: 9 < 10 is true (1) for numbers, but '9' < '10' is false (0) for text, since text compares character by character.
Keys: how a row is found
Every table should have a primary key, a column whose value identifies exactly one row, forever. Rules of thumb:
- Prefer a meaningless
id INTEGER PRIMARY KEY. It never needs to change. - Avoid "natural" keys like email or name: people change emails, and two customers can share a name. A key that changes breaks every foreign key pointing at it.
- Foreign keys (lesson 5-1) then point at that id, like
orders.customer_id.
This is why every table you have joined in this course had an id column.
Problem
A table stores books: title, the year published, price in dollars and cents (like 12.99), and the ISBN code '978-0441172719' (digits with dashes). Which SQLite type should the ISBN column be? Answer with one word.
Code exercise · sql
Your turn. The events table follows this lesson's advice: a meaningless INTEGER PRIMARY KEY and dates as 'YYYY-MM-DD' TEXT. Add an ORDER BY so the events print in chronological order, earliest first. It works because year-month-day text sorts like time.