SQLite CREATE TABLE: Syntax, Constraints, and Examples

CREATE TABLE Defines a Schema

Every piece of structured data in SQLite lives in a table, and every table starts with a CREATE TABLE statement. You give it a name, list the columns, and optionally attach constraints. SQLite writes the schema into the database file and the table is ready to use.

The smallest useful example:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com');
INSERT INTO users (name)        VALUES ('Boris');

SELECT * FROM users;

Three columns, one primary key, one NOT NULL rule. SQLite filled in id automatically because it's an integer primary key, and let email be NULL for the second row because nothing said otherwise. That's the whole shape — name, columns, constraints — and everything else in this page is a variation on it.

The Syntax, Piece by Piece

A column definition is name TYPE constraint constraint .... The type is optional in classic SQLite (more on that on the type affinity page), but it's good practice to always include one — readers and tools rely on it.

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    sku         TEXT    NOT NULL UNIQUE,
    name        TEXT    NOT NULL,
    price_cents INTEGER NOT NULL,
    in_stock    INTEGER NOT NULL DEFAULT 1
);

INSERT INTO products (sku, name, price_cents)
VALUES ('SKU-001', 'Notebook', 499);

SELECT * FROM products;

A few things worth pointing out:

• Constraints chain together with spaces: NOT NULL UNIQUE on sku means both rules apply.
• DEFAULT 1 on in_stock lets the INSERT skip that column.
• SQLite uses INTEGER for booleans — there's no native BOOLEAN type. 0 is false, 1 is true.
• Trailing comma after the last column is a syntax error. SQL is stricter than JavaScript here.

IF NOT EXISTS: Don't Crash on Re-Runs

Run a CREATE TABLE against a database that already has that table, and SQLite raises an error:

Error: table users already exists

That's fine the first time, annoying the hundredth. IF NOT EXISTS makes the statement a no-op when the table is already there:

CREATE TABLE IF NOT EXISTS users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

SELECT name FROM sqlite_master WHERE type = 'table';

The second CREATE TABLE does nothing — no error, no schema change. This is the form you want in startup code, migration scripts, and anywhere the same SQL might run more than once.

A warning, though: IF NOT EXISTS only checks the name. If a table with that name exists but has different columns, SQLite leaves it alone. It will not "fix" or "upgrade" the schema for you. That's what migrations are for.

Constraints: Rules That Travel With the Schema

Constraints are how you push validation into the database itself. The four you'll reach for constantly:

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    UNIQUE (user_id, created_at)
);

INSERT INTO orders (user_id, total_cents) VALUES (1, 1500);
INSERT INTO orders (user_id, total_cents, status) VALUES (1, 800, 'shipped');

SELECT * FROM orders;

• PRIMARY KEY — uniquely identifies a row. More on this in the primary keys doc.
• NOT NULL — the column must have a value.
• DEFAULT value — used when an INSERT omits the column. Can be a literal or an expression like datetime('now').
• CHECK (expr) — must evaluate true for every row.
• UNIQUE (col, col) — table-level constraint enforcing uniqueness across the combination.

Constraints run on every INSERT and UPDATE. A row that violates one is rejected and the statement fails. Catching bad data in the database is much cheaper than catching it after it's spread through the application.

Foreign Keys

A foreign key says "this column points at a row in another table." It keeps your data consistent — you can't reference a user that doesn't exist, and (with the right options) deleting a user can cascade to their orders.

PRAGMA foreign_keys = ON;

CREATE TABLE users (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE posts (
    id      INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title   TEXT NOT NULL
);

INSERT INTO users (name) VALUES ('Ada');
INSERT INTO posts (user_id, title) VALUES (1, 'Hello, world');

SELECT p.id, u.name, p.title FROM posts p JOIN users u ON u.id = p.user_id;

A SQLite gotcha worth memorising: foreign key enforcement is off by default. You have to run PRAGMA foreign_keys = ON for every connection that wants the constraints checked. Most application drivers either do this for you or expose a setting; if yours doesn't, run the pragma right after connecting.

ON DELETE CASCADE here means deleting a user automatically deletes their posts. Other options include SET NULL, RESTRICT, and the default — NO ACTION — which rejects the delete if children exist.

CREATE TABLE AS SELECT

Sometimes you want a quick copy of a query's result as a new table — for a snapshot, a backup, or a scratch table during analysis. CREATE TABLE ... AS SELECT does that:

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL
);

INSERT INTO products (name, price_cents) VALUES
    ('Notebook', 499), ('Pen', 199), ('Stapler', 1299);

CREATE TABLE expensive_products AS
SELECT id, name, price_cents
FROM products
WHERE price_cents >= 500;

SELECT * FROM expensive_products;

The new table copies the column names, types (best-effort), and the data. What it doesn't copy is just as important: no primary key, no NOT NULL, no indexes, no foreign keys. It's a flat snapshot. Treat it as a starting point for ad-hoc work, not a way to clone a real schema.

If you want the structure without the data, add WHERE 0:

CREATE TABLE products (
    id          INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL
);

CREATE TABLE products_archive AS SELECT * FROM products WHERE 0;

SELECT * FROM products_archive;
SELECT name FROM sqlite_master WHERE type = 'table';

You get an empty table with the same column shape — handy for archive tables you'll fill later.

Temporary Tables

A TEMP table lives only for the current database connection. Close the connection and it's gone — no cleanup, no leftover schema:

CREATE TEMP TABLE recent_signups (
    user_id    INTEGER PRIMARY KEY,
    signed_up  TEXT NOT NULL
);

INSERT INTO recent_signups VALUES (1, '2026-01-15'), (2, '2026-01-18');

SELECT * FROM recent_signups;

Good uses: staging rows for a multi-step query, holding intermediate results too messy for a CTE, isolating per-connection data in a long-running session. CREATE TEMP TABLE and CREATE TEMPORARY TABLE mean the same thing.

You can combine it with AS SELECT too: CREATE TEMP TABLE snapshot AS SELECT ... is a common pattern for freezing a result set partway through analysis.

Quoting Names

Most of the time, column and table names are bare identifiers. If you have to use a reserved word or a name with spaces, wrap it in double quotes (the SQL standard) or backticks (a MySQL-ism that SQLite also accepts):

CREATE TABLE "order" (
    id        INTEGER PRIMARY KEY,
    "select"  TEXT,
    "user id" INTEGER
);

INSERT INTO "order" ("select", "user id") VALUES ('a', 1);
SELECT * FROM "order";

It works, but it's friction every time you reference the table. Prefer plain names like orders, selection, user_id and skip the quoting entirely.

A Realistic Example

Pulling the pieces together — a small schema for a tasks app, with IF NOT EXISTS so it can run on every startup:

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS tasks (
    id          INTEGER PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title       TEXT    NOT NULL,
    done        INTEGER NOT NULL DEFAULT 0 CHECK (done IN (0, 1)),
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO tasks (user_id, title) VALUES (1, 'Write the docs');

SELECT u.name, t.title, t.done FROM tasks t JOIN users u ON u.id = t.user_id;

That's a schema you can ship: idempotent creation, foreign keys enforced, a CHECK keeping done honest, sensible defaults, and timestamps that fill themselves in.

Next: Data Types

CREATE TABLE lets you write INTEGER, TEXT, REAL — but SQLite is famously relaxed about how it stores those values. The next page covers the five storage classes SQLite actually uses, and why the type you wrote isn't always the type you get.