Creating Tables With CREATE TABLE in SQLite

CREATE TABLE was one of the very first SQL statements I properly learned, and I still think it’s one of the most important ones to truly master, because every design decision you make at table-creation time ripples through the rest of your application. In this article, I’ll walk through everything I’ve learned about creating tables in SQLite — from the basic syntax to constraints, data types, and some SQLite-specific quirks that trip up people coming from other databases.

The Basic Syntax

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

This creates a table with three columns: an integer primary key, a required username, and an optional email. Running this in the sqlite3 shell:

sqlite3 app.db

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

SQLite’s Storage Classes

Before diving deeper into CREATE TABLE, it helps to know that SQLite recognizes five storage classes: NULL, INTEGER, REAL, TEXT, and BLOB. When you declare a column type in CREATE TABLE, you’re actually declaring its type affinity — a preference for how values should be stored — rather than a hard constraint, unless you use STRICT tables (covered later).

CREATE TABLE example (
    a INTEGER,
    b TEXT,
    c REAL,
    d BLOB,
    e NUMERIC
);

The INTEGER PRIMARY KEY Special Case

This is one of the most important SQLite-specific details I had to learn. When a column is declared exactly as INTEGER PRIMARY KEY, it becomes an alias for SQLite’s internal rowid, giving you automatic, efficient auto-incrementing behavior without extra configuration.

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

INSERT INTO products (name) VALUES ('Keyboard');
SELECT id FROM products;
-- automatically assigned, starting from 1

AUTOINCREMENT: When You Actually Need It

Many tutorials add AUTOINCREMENT reflexively, but I’ve learned it’s usually unnecessary in SQLite. Without it, INTEGER PRIMARY KEY already auto-generates increasing values, and it will reuse a previously deleted row’s ID under specific conditions. AUTOINCREMENT guarantees IDs are never reused, even after deletions, at a small performance cost.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    total REAL NOT NULL
);

I only reach for AUTOINCREMENT when I have a genuine business requirement that IDs must never be reused — for example, in financial or audit-related tables.

Column Constraints

NOT NULL

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

UNIQUE

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE
);

DEFAULT

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    is_published INTEGER DEFAULT 0
);

I use DEFAULT CURRENT_TIMESTAMP constantly for created-at style columns, since it removes the need to explicitly set the timestamp from application code on every insert.

CHECK

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    price REAL NOT NULL CHECK (price >= 0)
);

INSERT INTO products (price) VALUES (-5);
-- Error: CHECK constraint failed: products

CHECK constraints let me encode business rules directly into the schema, so invalid data is rejected at the database level regardless of what the application layer does.

Table-Level Constraints

Some constraints apply across multiple columns rather than just one, and these are defined at the table level instead of inline with a column.

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

This creates a composite primary key across two columns — useful for many-to-many relationship tables like this one, where the combination of student and course must be unique, even though neither column alone is.

Foreign Keys

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

An important detail I had to learn the hard way: SQLite doesn’t enforce foreign key constraints by default. You have to explicitly enable enforcement for every connection:

PRAGMA foreign_keys = ON;

Without this, SQLite will happily let you insert an order referencing a user_id that doesn’t exist in the users table at all.

Cascading Behavior

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

ON DELETE CASCADE automatically deletes related orders when the referenced user is deleted — genuinely useful, but something I’m always careful with, since it can silently delete more data than expected if the relationships aren’t fully thought through.

STRICT Tables

For stronger type enforcement than SQLite’s default dynamic typing, newer versions support STRICT tables:

CREATE TABLE strict_products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL
) STRICT;

INSERT INTO strict_products (id, name, price) VALUES (1, 'Mouse', 'not a number');
-- Error: cannot store TEXT value in REAL column price

I’ve started using STRICT tables in newer projects specifically because it catches type mistakes much earlier than SQLite’s default flexible typing would.

WITHOUT ROWID Tables

By default, every SQLite table has a hidden rowid column unless you declare it WITHOUT ROWID, which is a performance-oriented option for tables where you already have a natural, efficient primary key and don’t need the extra rowid overhead.

CREATE TABLE settings (
    key TEXT PRIMARY KEY,
    value TEXT
) WITHOUT ROWID;

I use WITHOUT ROWID selectively — mainly for tables with a non-integer primary key that’s already efficiently indexed, like a text-based key-value table.

IF NOT EXISTS

CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY,
    message TEXT
);

This is a pattern I use in almost every setup script — it prevents an error if the table already exists, which is especially useful for idempotent migration or initialization scripts.

Creating a Table From an Existing Query

CREATE TABLE recent_orders AS
SELECT * FROM orders WHERE ordered_at > '2024-01-01';

This is a quick way to snapshot or materialize the results of a query into a new physical table — genuinely handy for reporting or archiving use cases, though the new table won’t automatically inherit constraints like NOT NULL or foreign keys from the original.

Generated Columns

CREATE TABLE rectangle (
    width REAL,
    height REAL,
    area REAL GENERATED ALWAYS AS (width * height) STORED
);

Generated columns compute their value from other columns automatically — STORED persists the computed value on disk, while VIRTUAL (the default if unspecified) recomputes it on every read instead of storing it.

A Realistic, Complete Example

PRAGMA foreign_keys = ON;

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

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0),
    category_id INTEGER,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) STRICT;

This single schema demonstrates primary keys, foreign keys, check constraints, default values, cascading behavior, and strict typing all working together.

Best Practices

• Always declare an explicit primary key — don’t rely on the implicit rowid alone without giving it a meaningful name.
• Enable PRAGMA foreign_keys = ON; for every connection where referential integrity matters, since it’s off by default.
• Use CHECK constraints to encode business rules directly into the schema wherever practical.
• Reach for STRICT tables in new projects if you want stronger type safety than SQLite’s default dynamic typing provides.
• Use CREATE TABLE IF NOT EXISTS in setup and migration scripts to make them safely re-runnable.
• Think carefully before using ON DELETE CASCADE — make sure cascading deletes are genuinely the intended behavior, not just a convenient default.

Frequently Asked Questions

Do I need AUTOINCREMENT on every primary key? No — plain INTEGER PRIMARY KEY already auto-generates increasing values in most cases. Use AUTOINCREMENT only when you specifically need a guarantee that IDs are never reused after deletion.

Are foreign keys enforced automatically in SQLite? No, you must explicitly run PRAGMA foreign_keys = ON; for each connection, since it’s disabled by default for backward-compatibility reasons.

What’s the difference between STRICT and regular tables? Regular tables use flexible type affinity, allowing mismatched types to be stored more permissively. STRICT tables enforce column types much more rigidly, rejecting mismatched data outright.

When should I use WITHOUT ROWID? When your table already has an efficient, natural primary key (often text-based) and you want to avoid the overhead of SQLite’s default hidden rowid column.

Can I add constraints to a table after it’s created? SQLite’s ALTER TABLE support is limited — for most constraint changes, the standard approach is creating a new table with the desired structure, copying the data over, and renaming it.

Wrapping Up

CREATE TABLE looks simple on the surface, but there’s genuinely a lot of nuance packed into it, especially in SQLite, where dynamic typing, the special INTEGER PRIMARY KEY behavior, and opt-in features like STRICT and WITHOUT ROWID all interact in ways that aren’t always obvious from other database backgrounds. Taking the time to actually understand these details up front, rather than copying a generic template every time, has saved me from a lot of subtle schema-related bugs down the line.