Chapter 11 · Defining Databases and Tables
CREATE TABLE and Column Definitions
CREATE TABLE converts a data contract into an enforceable structure. Every column definition should preserve meaning, constrain invalid states, and communicate how the table will be queried and maintained.
Learning outcomes
A table definition is a durable contract among stored values, SQL queries, application code, migrations, and operators. Good column definitions make invalid states difficult to store and valid states easy to understand.
Translate a logical entity into table and column definitions.
Choose column names, types, nullability, and defaults deliberately.
Use SQLite STRICT tables to reduce accidental type coercion.
Define generated columns for deterministic derived values.
Inspect the stored schema and verify that it matches the intended contract.
From entity to physical table
CREATE TABLE is the point where conceptual meaning becomes executable database structure.
The anatomy of CREATE TABLE
CREATE TABLE course ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3, capacity INTEGER NOT NULL DEFAULT 30, published INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;| Part | Question answered |
|---|---|
| Table name | What durable entity or relationship does this table represent? |
| Column name | What fact does one value carry? |
| Declared type | Which representation and operations are valid? |
| NULL / NOT NULL | May the fact be unknown or inapplicable? |
| DEFAULT | What value is produced when the writer omits the column? |
| Constraint | Which values or combinations are forbidden? |
Column naming is interface design
Specific nouns
Prefer ordered_at, unit_price_cents, and customer_id over vague names.
Encode units
Use names such as duration_seconds or document the unit in a data contract.
Avoid overloaded flags
A single status domain is often clearer than several booleans that can contradict each other.
Separate concepts
Creation time, event time, effective time, and update time are different facts.
SQLite storage classes and STRICT tables
Ordinary SQLite tables use type affinity and can accept values whose storage class differs from the declared type. A STRICT table restricts declared types and rejects values that cannot be losslessly converted.
CREATE TABLE measurement ( measurement_id INTEGER PRIMARY KEY, sensor_code TEXT NOT NULL, reading REAL NOT NULL, captured_at TEXT NOT NULL) STRICT;INSERT INTO measurement (sensor_code, reading, captured_at)VALUES ('TEMP-01', 23.75, '2026-08-05T07:30:00Z');-- Fails in a STRICT table because the reading is not numeric.INSERT INTO measurement (sensor_code, reading, captured_at)VALUES ('TEMP-01', 'not-a-number', '2026-08-05T07:31:00Z');It controls broad storage types. Use CHECK, foreign keys, and application validation for ranges, formats, and cross-row business rules.
Defaults: omission, not repair
CREATE TABLE processing_job ( job_id INTEGER PRIMARY KEY, job_name TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'queued', retry_count INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;INSERT INTO processing_job (job_name)VALUES ('refresh-course-catalog')RETURNING job_id, state, retry_count, created_at;A default runs when the column is omitted or the keyword DEFAULT is used. An explicit NULL does not mean “use the default” when the column is nullable, and it violates NOT NULL when the column is required.
Generated columns
CREATE TABLE invoice_line ( invoice_id INTEGER NOT NULL, line_no INTEGER NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0), line_total_cents INTEGER GENERATED ALWAYS AS (quantity * unit_price_cents) STORED, PRIMARY KEY (invoice_id, line_no)) STRICT;INSERT INTO invoice_line (invoice_id, line_no, quantity, unit_price_cents)VALUES (9001, 1, 3, 1299);SELECT quantity, unit_price_cents, line_total_centsFROM invoice_line;A generated expression should be deterministic and derived solely from the same row. Do not duplicate values that may legitimately diverge or depend on external state.
Temporary tables and CREATE TABLE AS
CREATE TEMP TABLE published_course_snapshot ASSELECT course_id, course_code, title, creditsFROM courseWHERE published = 1;SELECT *FROM published_course_snapshotORDER BY course_code;CREATE TABLE AS SELECT derives column names and values from a query but does not copy source constraints, primary keys, or defaults. It is useful for transient snapshots, not as a substitute for a carefully designed durable table.
WITHOUT ROWID and composite identity
CREATE TABLE enrollment_v2 ( course_id INTEGER NOT NULL, student_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'enrolled', PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;For tables naturally identified by a composite primary key, WITHOUT ROWID can avoid maintaining a separate hidden rowid structure. Measure before using it as a universal convention.
Reusable Chapter 11 practice schema
Run this SQLite script in a disposable database before the hands-on exercises. It establishes a small academic domain with strict tables, generated data, composite uniqueness, foreign keys, a view, and representative rows.
PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS active_course_catalog;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS department;CREATE TABLE department ( department_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE, budget_cents INTEGER NOT NULL DEFAULT 0 CHECK (budget_cents >= 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, hired_on TEXT NOT NULL CHECK (date(hired_on) IS NOT NULL), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), instructor_id INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6), capacity INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), display_name TEXT GENERATED ALWAYS AS (course_code || ' · ' || title) VIRTUAL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (department_id, course_code)) STRICT;CREATE TABLE enrollment ( course_id INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE, student_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'completed', 'withdrawn')), PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;CREATE VIEW active_course_catalog ASSELECT c.course_id, d.code AS department_code, c.course_code, c.title, c.credits, c.capacityFROM course AS cJOIN department AS d ON d.department_id = c.department_idWHERE c.published = 1;INSERT INTO department (department_id, code, name, budget_cents) VALUES (1, 'DATA', 'Data Engineering', 25000000), (2, 'CS', 'Computer Science', 30000000);INSERT INTO instructor (instructor_id, department_id, email, full_name, hired_on)VALUES (10, 1, 'nadia@example.edu', 'Nadia Rahimi', '2024-09-01'), (11, 2, 'omar@example.edu', 'Omar Haddad', '2023-02-15');INSERT INTO course (course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES (100, 1, 10, 'SQL-101', 'SQL Foundations', 3, 40, 1), (101, 1, 10, 'DE-201', 'Data Pipelines', 4, 30, 1), (102, 2, 11, 'DB-220', 'Database Systems',4, 35, 0);INSERT INTO enrollment (course_id, student_id, status) VALUES (100, 1001, 'enrolled'), (100, 1002, 'completed'), (101, 1001, 'enrolled');Inspect the stored contract
SELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE name IN ( 'department', 'instructor', 'course', 'enrollment', 'active_course_catalog')ORDER BY type, name;PRAGMA table_xinfo('course');PRAGMA table_list;table_xinfo includes generated and hidden columns that a simpler table inspection may omit. Treat the actual stored schema as the final authority during validation.
Practice: design an assessment table
CREATE TABLE assessment ( assessment_id INTEGER PRIMARY KEY, course_id INTEGER NOT NULL REFERENCES course(course_id), title TEXT NOT NULL CHECK (length(trim(title)) > 0), weight_basis_points INTEGER NOT NULL CHECK (weight_basis_points BETWEEN 0 AND 10000), due_at TEXT, published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE UNIQUE INDEX uq_assessment_course_titleON assessment (course_id, title);Checkpoint
Review the table contract
- Why should physical column names include units when ambiguity is possible?
- What does SQLite STRICT enforce, and what does it not enforce?
- When is a default evaluated?
- What constraints are lost by CREATE TABLE AS SELECT?
- When can WITHOUT ROWID be appropriate?
Review the answers
Names communicate meaning and units. STRICT controls broad storage types but not full business domains. Defaults apply when a column is omitted. CTAS does not inherit source constraints. WITHOUT ROWID can suit compact tables whose natural primary key is composite.
Summary and references
- CREATE TABLE is an executable data contract.
- Names, types, nullability, defaults, and generated values should preserve meaning.
- STRICT tables improve SQLite’s type boundary but still require domain constraints.
- Schema introspection verifies the contract actually stored by the engine.