Designing a table

Column by column

CREATE TABLE books (title TEXT NOT NULL, year INTEGER); reads like the shape it makes: a name, a type, and any constraints on what may be stored. The common types are INTEGER for whole numbers, REAL for decimals, and TEXT for everything else.

SQLite is relaxed about types — a TEXT column will happily hold a number — but the declared type is what tells a reader and a tool what the column means, and it is what a stricter database would enforce. Declare the type you mean.

Constraints are the rules of the table

A PRIMARY KEY names the one column that identifies each row; it must be unique and never missing. NOT NULL refuses a row without that value. UNIQUE allows the column to be empty but not repeated. DEFAULT fills in a value when the INSERT does not mention the column.

These are the promises the rest of the program gets to rely on. A title that cannot be NULL means no query ever has to ask whether it is there; a unique id means a lookup by id returns at most one row by construction.

CHECK (year > 0) is the constraint that encodes a rule about the value itself, rather than about presence or uniqueness. It is tested on every insert and update, and a row that fails is refused. Give it a name — CONSTRAINT year_positive CHECK (year > 0) — because that name is what the next person sees in the error message.

Links between tables

A foreign key records that one table points at another: author_id INTEGER REFERENCES authors(id). It documents the relationship and gives a tool something to check — a book whose author does not exist is exactly the kind of row that ends up in a report as a mystery.

SQLite only enforces foreign keys when that support is switched on for the connection. Treat the clause as the schema telling the truth about itself, and the enforcement as a setting the application chooses.

Types are affinity, not a cage

SQLite gives every column a type affinity rather than a hard type. A column declared TEXT stores text, but a number inserted into it is converted to text; a column declared INTEGER converts a numeric string to a number. The declared type tells the database which conversion to prefer, and tells the reader what the column means.

Other databases enforce the type at the door. SQLite's flexibility is useful for prototypes and a trap for data: a TEXT column converts a number to text on the way in, while a column with no declared type can hold '12' in one row and 12 in another and compare them differently. If the type matters, declare it and insert the type you mean.

A STRICT table, available since SQLite 3.37, turns the affinity into an actual rule. This course keeps to ordinary tables so the examples run anywhere, but the option is worth knowing when a schema has to hold the line.

Defaults and identity

DEFAULT is applied only when the column is omitted from the INSERT. Writing NULL for that column is not an omission — it is a value, and it is stored, unless NOT NULL refuses it.

INTEGER PRIMARY KEY is special in SQLite: it becomes an alias for the hidden rowid, and leaving it out of an INSERT assigns the next number automatically. AUTOINCREMENT adds a guarantee that ids are never reused, at a small cost; plain INTEGER PRIMARY KEY is enough for most tables.

A UNIQUE column accepts any number of NULLs, because two NULLs are not equal. If the column must be present and unique, it needs NOT NULL UNIQUE — two constraints, not one.

A small table, with the rules written in

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  country TEXT,
  active INTEGER NOT NULL DEFAULT 1
);

INSERT INTO authors (name, country) VALUES ('Ursula K. Le Guin', 'United States');
ALTER TABLE authors ADD COLUMN born INTEGER;

Try these next

• Add a CHECK (year > 0) to a copy of a table and insert a negative year to read the error.
• Create a table with a two-column primary key and try to insert the same pair twice.
• Insert one row without the defaulted column and one with an explicit NULL, then compare.