SQLite § data-structures

Tables and rowid

The table is the fundamental data structure of an SQL database, and SQLite is no exception. A table is a named collection of rows with a fixed schema of named, typed columns and an associated set of constraints. This page documents CREATE TABLE, the rowid mechanism that distinguishes SQLite from other engines, the alternative WITHOUT ROWID storage layout, the STRICT keyword for opt-in classical typing, the ALTER TABLE statement and its limited surface, and the DROP TABLE statement together with the table-introspection mechanisms that the engine provides.

CREATE TABLE

The CREATE TABLE statement defines a table’s schema. The minimal form is a name and a list of column definitions:

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    phone TEXT,
    email TEXT UNIQUE
);

A column definition consists of a column name, an optional declared type, and zero or more column constraints. Column constraints attach to a single column; table constraints may reference several columns and follow the column list:

CREATE TABLE bouquet_flower (
    bouquet_id INTEGER NOT NULL,
    flower_id  INTEGER NOT NULL,
    quantity   INTEGER NOT NULL DEFAULT 1,
    PRIMARY KEY (bouquet_id, flower_id),
    FOREIGN KEY (bouquet_id) REFERENCES bouquet(id),
    FOREIGN KEY (flower_id)  REFERENCES flower(id)
);

Constraints are documented in detail under Constraints; the present page covers only the structural elements of CREATE TABLE.

Two prefix qualifiers modify the statement:

IF NOT EXISTS suppresses the error if the named table already exists. The schema of the existing table is not checked against the new declaration; the statement becomes a no-op.
TEMP (or TEMPORARY) creates the table in the temporary database, where it persists for the duration of the connection and is invisible to other connections.

CREATE TABLE IF NOT EXISTS audit_log (...);
CREATE TEMP TABLE scratch (...);

CREATE TABLE … AS SELECT

A table can be populated from a query in a single statement. The schema is inferred from the query’s column expressions; all columns become nullable and acquire BLOB affinity unless the query’s projection consists of bare column references whose source columns have other affinities.

CREATE TABLE archive_2024 AS
    SELECT * FROM event WHERE strftime('%Y', occurred_at) = '2024';

The constraint set of the source table is not propagated. Indexes, primary keys, foreign keys, and NOT NULL constraints must be added explicitly after the fact. The conventional use is to copy a snapshot of a query result for archival or for offline processing; the form is rarely the right choice for primary application tables.

Rowid

Every conventional SQLite table has an implicit, hidden integer column called the rowid (also accessible as _rowid_ or oid). The rowid is a 64-bit signed integer, automatically assigned by the engine when a row is inserted, and is the primary key of the table at the storage layer. The B-tree on disk is keyed on the rowid; row lookups by rowid are an O(log n) indexed search; row lookups by any other criterion may require a table scan unless an index is present.

A rowid that has not been explicitly assigned is one greater than the maximum rowid currently in the table, or one if the table is empty. Rowids of deleted rows can be reused; the AUTOINCREMENT keyword on INTEGER PRIMARY KEY columns prevents reuse and is the conventional choice when a stable identifier is required (see Constraints).

The rowid is queryable like any other column:

SELECT rowid, name FROM customer;

When a column is declared INTEGER PRIMARY KEY, that column becomes an alias for the rowid; assignments to it set the rowid, and the engine stores no separate column. This is why INTEGER PRIMARY KEY is the recommended form for the primary key of a conventional rowid table:

CREATE TABLE customer (
    id    INTEGER PRIMARY KEY,   -- alias for rowid
    name  TEXT NOT NULL
);

The alias must be exactly the type name INTEGER; the syntactic variant INT PRIMARY KEY is not an alias, and the column is a separate B-tree key from the rowid. This is one of SQLite’s idiosyncratic distinctions and is worth remembering.

WITHOUT ROWID

A table declared WITHOUT ROWID has no implicit rowid. The primary key is the storage-layer key directly: rows are stored in primary-key order on disk, and lookups by primary key are slightly faster (one fewer B-tree traversal). The mechanism is appropriate for tables whose primary key is a string or composite, where the implicit-rowid layout would require a second B-tree.

CREATE TABLE word_count (
    word  TEXT NOT NULL,
    count INTEGER NOT NULL,
    PRIMARY KEY (word)
) WITHOUT ROWID;

The principal trade-off: WITHOUT ROWID tables cannot be FTS5 content tables, cannot be incrementally BLOB-streamed (the sqlite3_blob_open API requires a rowid), and lose the convenient last_insert_rowid API. The recommendation in the SQLite documentation is to consider WITHOUT ROWID for tables with non-integer primary keys, especially when the row size is small or the primary key is the natural lookup column.

STRICT tables

STRICT tables, introduced in version 3.37, enforce conventional column typing in place of the default type-affinity discipline. A STRICT table is declared with the keyword after the closing parenthesis of the column list. The declared type of every column must be one of the six accepted strict types: INT, INTEGER, REAL, TEXT, BLOB, or ANY.

CREATE TABLE order_strict (
    id     INTEGER PRIMARY KEY,
    amount REAL    NOT NULL,
    note   TEXT
) STRICT;

INSERT INTO order_strict VALUES (1, 'forty', 'a note');
-- Error: cannot store TEXT value in REAL column order_strict.amount

STRICT tables behave like the columns of a strictly typed engine: insertions of values that are not the declared type fail. The ANY declared type accepts any of the five storage classes and is the opt-out within an otherwise strict table. The recommendation in new code is to declare STRICT tables unless there is a specific reason to want the permissive form (compatibility with old applications, for instance, or schema evolution that takes advantage of affinity-based migrations).

STRICT and WITHOUT ROWID may be combined; the two qualifiers are independent and both apply.

CREATE TABLE word_count (
    word  TEXT NOT NULL,
    count INTEGER NOT NULL,
    PRIMARY KEY (word)
) STRICT, WITHOUT ROWID;

ALTER TABLE

SQLite’s ALTER TABLE surface is deliberately limited compared to other engines. The supported forms are:

Operation	Statement
Rename the table	`ALTER TABLE old RENAME TO new;`
Rename a column	`ALTER TABLE t RENAME COLUMN old TO new;`
Add a column	`ALTER TABLE t ADD COLUMN col TYPE constraints;`
Drop a column	`ALTER TABLE t DROP COLUMN col;`

The RENAME COLUMN form was added in 3.25 (2018). The DROP COLUMN form was added in 3.35 (2021). Earlier versions required the table-rebuild dance: create a new table with the desired schema, copy data with an INSERT … SELECT, drop the old table, rename the new one. The dance is still required for unsupported operations such as changing a column’s type, changing a constraint, or reordering columns.

-- Rename a table
ALTER TABLE customer RENAME TO client;

-- Add a nullable column with a default
ALTER TABLE client ADD COLUMN created_at TEXT DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'));

-- Rename a column (3.25+)
ALTER TABLE client RENAME COLUMN phone TO phone_number;

-- Drop a column (3.35+)
ALTER TABLE client DROP COLUMN phone_number;

ADD COLUMN has two practical restrictions: the new column cannot be PRIMARY KEY, and a NOT NULL column without a DEFAULT is rejected (existing rows would acquire NULL in the new column, violating the constraint). The conventional remedy is to add the column nullable, populate it with an UPDATE, and then enforce non-NULL through a CHECK constraint (or rebuild the table with the desired constraint set).

DROP COLUMN has restrictions of its own: the dropped column may not be part of the primary key, may not be referenced by a foreign key from another table, and may not be referenced by an index, view, or trigger. The conventional remedy is to drop the dependent objects, drop the column, and recreate the dependents.

For schema evolutions outside this surface — changing column types, reordering columns, changing constraints — the table-rebuild dance remains the recommended approach:

PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;

CREATE TABLE customer_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE        -- now NOT NULL
);

INSERT INTO customer_new (id, name, email)
SELECT id, name, COALESCE(email, name || '@unknown') FROM customer;

DROP TABLE customer;
ALTER TABLE customer_new RENAME TO customer;

COMMIT;
PRAGMA foreign_keys = ON;

The PRAGMA foreign_keys = OFF brackets are necessary if other tables reference the renamed table; the rename does not automatically update the foreign-key references in the referencing tables, and the constraint check is suspended for the duration of the transaction.

DROP TABLE

DROP TABLE removes a table and all of its rows, indexes, and triggers from the database. The optional IF EXISTS clause suppresses the error if the table does not exist.

DROP TABLE customer;
DROP TABLE IF EXISTS customer;

If the table is referenced by a foreign key from another table and PRAGMA foreign_keys = ON, the drop fails. The conventional remedy is to drop the referencing table first, or to drop the foreign-key constraint via a table rebuild on the referencing table.

DROP TABLE cannot be undone outside a transaction. Inside a transaction, ROLLBACK restores the table; the recommendation is to wrap any administrative drops in BEGIN / COMMIT so that mistakes are recoverable.

Inspecting tables

The schema is exposed through the system table sqlite_master (or its alias sqlite_schema), which holds the CREATE statement of every table, index, view, and trigger:

SELECT name, type, sql FROM sqlite_master WHERE type = 'table';

The PRAGMA family exposes finer-grained introspection:

PRAGMA table_info(customer);          -- columns: cid, name, type, notnull, dflt_value, pk
PRAGMA table_xinfo(customer);         -- includes hidden columns (3.26+)
PRAGMA index_list(customer);          -- indexes on the table
PRAGMA index_info(idx_customer_email); -- columns of an index
PRAGMA foreign_key_list(employee);    -- foreign keys outgoing from a table

The sqlite3 shell .dot commands .tables and .schema are interactive forms of the same queries:

.tables                # list tables
.schema customer       # show CREATE TABLE statement
.indexes customer      # list indexes on the table

A worked schema

A small worked example, illustrating the conventional choices for a new SQLite schema:

CREATE TABLE customer (
    id      INTEGER PRIMARY KEY,
    name    TEXT NOT NULL,
    email   TEXT UNIQUE COLLATE NOCASE,
    phone   TEXT,
    created TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

CREATE TABLE bouquet (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
) STRICT;

CREATE TABLE bouquet_order (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customer(id) ON DELETE RESTRICT,
    bouquet_id  INTEGER NOT NULL REFERENCES bouquet(id)  ON DELETE RESTRICT,
    quantity    INTEGER NOT NULL CHECK (quantity > 0) DEFAULT 1,
    placed_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
) STRICT;

CREATE INDEX ix_order_customer ON bouquet_order (customer_id, placed_at);
CREATE INDEX ix_order_bouquet  ON bouquet_order (bouquet_id);

The choices to note: INTEGER PRIMARY KEY for surrogate keys (rowid alias); STRICT for type enforcement; NOT NULL on every column without a meaningful NULL; UNIQUE with COLLATE NOCASE on the email; CHECK constraints for domain restrictions; REFERENCES … ON DELETE RESTRICT for referential integrity; covering indexes on the foreign-key columns. The schema is small but exercises most of the constraint surface; subsequent pages elaborate on each decision.