CREATE TABLE in SQLite: Schema, sqlite_schema, and Introspection

Learning outcomes

In the prerequisite SQL and modeling courses, a table is a relation-shaped structure with named columns, keys, and constraints. SQLite implements those ideas through concrete schema objects stored inside each database. The important habit for this chapter is therefore: declare, then inspect. Do not assume the engine created exactly what you intended merely because a CREATE TABLE statement returned no error.

Start from the relational idea, then ask SQLite where it lives

A logical model says that FieldNotes needs objects such as sites, devices, and maintenance notes. DDL turns that model into database objects. In SQLite, the database named main is normally the file opened by the connection. A table created without a schema qualifier and without TEMP is created in main.

CREATE TABLE site_probe (    site_id   INTEGER PRIMARY KEY,    site_code TEXT NOT NULL,    site_name TEXT NOT NULL);

This statement declares a table; it does not insert any rows. The durable state change is the schema object itself. Chapter 4 will examine SQLite type behavior in depth; here, declared types are used only to make the schema readable and intentional.

CREATE TABLE can target main, temp, or an attached schema

SQLite allows a schema name before the table name. The built-in names are main for the primary database and temp for the connection's temporary schema. Additional names appear after ATTACH DATABASE, which Lesson 4 introduces.

CREATE TABLE main.main_example (    id INTEGER PRIMARY KEY,    note TEXT);CREATE TEMP TABLE scratch_example (    label TEXT);-- Equivalent placement for a temporary object:CREATE TABLE temp.scratch_example_2 (    label TEXT);

TEMP and TEMPORARY are synonyms here. If TEMP is present, the object belongs to the temp schema. Do not combine TEMP with an unrelated schema name such as main.some_table; SQLite rejects that contradictory request.

sqlite_schema is the database's schema catalog

Every SQLite database has a schema table conventionally exposed as sqlite_schema. It contains one row for each table, index, view, and trigger in that schema, apart from the schema table itself. Older applications and tutorials often use sqlite_master; SQLite still accepts that historical alias for compatibility.

SELECT type,       name,       tbl_name,       rootpage,       sqlFROM main.sqlite_schemaWHERE name NOT LIKE 'sqlite_%'ORDER BY type, name;

The sql column stores normalized CREATE text for user-defined objects. It is useful evidence, but not an excuse to edit the catalog directly. SQLite maintains this table as DDL executes. Direct schema-table manipulation is an expert-only recovery/migration technique with corruption risk and is outside this beginner workflow.

.schema and PRAGMAs answer different inspection questions

.schema is a sqlite3 shell command. It asks the database for schema information and renders DDL-like output for a human. PRAGMAs are SQLite statements that return structured metadata as rows, which makes them easier to query, compare, or use from application code.

.schema site_probePRAGMA main.table_info('site_probe');PRAGMA main.table_xinfo('site_probe');PRAGMA main.table_list('site_probe');

table_info returns normal columns and their declared type, not-null flag, default, and primary-key position. table_xinfo adds generated and hidden columns when those exist. table_list reports table-level facts such as schema name, column count, whether the table is WITHOUT ROWID, and whether it is STRICT.

IF NOT EXISTS prevents one error; it does not compare schemas

IF NOT EXISTS means “if an object with this name already satisfies the command's existence check, do nothing rather than raise the usual creation error.” It does not mean “compare the existing table to this declaration and migrate differences.” That distinction is critical in installers and migrations.

DROP TABLE IF EXISTS drift_demo;CREATE TABLE drift_demo (    id      INTEGER PRIMARY KEY,    message TEXT);-- No error, but this does NOT add created_at:CREATE TABLE IF NOT EXISTS drift_demo (    id         INTEGER PRIMARY KEY,    message    TEXT,    created_at TEXT NOT NULL);PRAGMA table_info('drift_demo');SELECT sqlFROM sqlite_schemaWHERE type='table' AND name='drift_demo';

The expected metadata still contains only id and message. SQLite produced that result because the second CREATE became a no-op after it found the existing object. Safe migration code needs explicit versioning and verification; Chapter 17 will build that discipline.

Schema-inspection lab: declared DDL versus engine metadata

Create a disposable file named chapter03_schema.db. Use a fresh file so results are reproducible and do not collide with the Chapter 1–2 database.

sqlite3 chapter03_schema.dbCREATE TABLE main.device_model (    model_code TEXT PRIMARY KEY,    model_name TEXT NOT NULL,    vendor     TEXT,    active     INTEGER NOT NULL DEFAULT 1);CREATE TEMP TABLE inspection_notes (    question TEXT,    answer   TEXT);.schema device_modelSELECT type, name, tbl_name, sqlFROM main.sqlite_schemaWHERE name='device_model';PRAGMA main.table_info('device_model');PRAGMA main.table_xinfo('device_model');PRAGMA main.table_list('device_model');SELECT type, name, sqlFROM temp.sqlite_schemaWHERE name='inspection_notes';

Expected durable state: main.device_model is stored in chapter03_schema.db. Expected transient state: temp.inspection_notes exists only for this connection. The catalog query and PRAGMAs should agree on the table name and declared columns even though they expose different metadata shapes.

Failure patterns and safe corrections

Summary and bridge to ROWID

SQLite schema design begins with a logical model but must end with inspection of the concrete objects the engine created. You can now distinguish main from temp, read sqlite_schema, use structured PRAGMAs, and explain why existence checks are weaker than schema verification. The next lesson asks a deeper question: when SQLite creates an ordinary table, what key actually organizes its rows?

References

• SQLite — CREATE TABLE.
• SQLite — The Schema Table.
• SQLite — PRAGMA Statements.
• SQLite — Command-Line Shell.
• SQLite — Release History.