Choosing Types and Keys

From lesson 7-3, INTEGER PRIMARY KEY in SQLite gives you auto-numbering on top of uniqueness. The column assigns the next number by itself when an insert leaves it out.

That is why the users rows in lesson 7-3 came out as 1 and 2 without any ids being typed. This lesson looks at how to choose that key, and the type of every other column alongside it.

Types: what a column may hold

Designing a schema, meaning the whole set of tables, columns, and rules, starts with picking a type for each column. These are the everyday SQLite types:

Dates are usually stored as TEXT in 'YYYY-MM-DD' form, which sorts correctly because the year comes first, then the month, then the day, so alphabetical order is also chronological order. Boolean values, meaning true or false answers such as whether an account is active, are stored as INTEGER 1 and 0, since SQLite has no separate boolean type.

Types matter because data stored as the wrong type misbehaves in ways that are easy to miss. The classic case is numbers kept as text, where sorting turns alphabetical: as text, '9' is greater than '10', because the character 9 comes after the character 1.

The next block proves it, using typeof(), which reports what a value actually is.

Comparing numbers against text

The first line reports the type of each literal. Then the same comparison runs twice, once on numbers and once on the same digits written as text.

SELECT typeof(42), typeof(4.2), typeof('hi'), typeof(NULL);
SELECT 9 < 10;
SELECT '9' < '10';

Output

integer|real|text|null
1
0

SQLite reports true as 1 and false as 0, so 9 < 10 gives 1 and is correct arithmetic. The text version gives 0, because text compares character by character: 9 against 1 decides the whole comparison on the first character, and 9 sorts later.

This is precisely what goes wrong when a numeric column is declared TEXT. Version numbers, prices imported from a spreadsheet, and quantities pasted from a form all show the symptom, where a sorted report puts 9 above 10 and nobody can see why.

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.

Picking a type for an identifier

For a books table holding a title, a publication year, a price like 12.99, and an ISBN such as '978-0441172719', the ISBN column should be TEXT.

Two clues point that way. The value contains dashes, which no numeric type can store, and it can begin with a zero, which an integer would silently discard. There is also no operation that makes sense: nobody adds two ISBNs together.

The same reasoning covers phone numbers, zip codes, and account numbers. They look numeric and are not, and storing them as integers is how leading zeros disappear from a postal code.

Sorting dates stored as text

The events table follows this lesson's advice, with a meaningless INTEGER PRIMARY KEY and dates as 'YYYY-MM-DD' text, so an ordinary ORDER BY puts them in chronological order.

CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  happens_on TEXT
);

INSERT INTO events (title, happens_on) VALUES ('Launch', '2024-03-01');
INSERT INTO events (title, happens_on) VALUES ('Beta', '2023-11-15');
INSERT INTO events (title, happens_on) VALUES ('Demo day', '2024-01-20');

SELECT title, happens_on FROM events ORDER BY happens_on;

Output

Beta|2023-11-15
Demo day|2024-01-20
Launch|2024-03-01

ORDER BY from lesson 3-1 works on text columns just as it does on numbers, and ascending is the default. The 2023 event lands first even though it was inserted second, because sorting has nothing to do with insertion order.

The whole trick rests on the field widths being fixed. 2024-03-01 keeps its zero-padded month and day, so every date is exactly ten characters and character-by-character comparison matches real time. A format like 1/3/2024 breaks completely, sorting all the January dates together regardless of year.