SQLite CREATE TABLE – Syntax, Data Types, Constraints, and Examples | DbSchema

Table of Contents

1. SQLite CREATE TABLE syntax
2. SQLite data types and type affinity
3. Column and table constraints
4. Create a table in the sqlite3 CLI
5. Create a table from Python
6. Useful CREATE TABLE patterns
7. Create tables visually in DbSchema
8. Performance and design tips
9. FAQ
10. Conclusion
11. References

The SQLite CREATE TABLE statement defines the structure of your data: column names, data types, primary keys, default values, checks, and relationships. It is one of the most important statements in SQLite because good table design affects data quality, query speed, and how easy the schema is to maintain later.

This guide covers the syntax, SQLite's flexible typing model, common constraints, CLI and Python examples, and how to create tables visually in DbSchema.

SQLite CREATE TABLE syntax

The general syntax looks like this:

CREATE TABLE IF NOT EXISTS table_name (
    column_name data_type column_constraint,
    column_name data_type column_constraint,
    table_constraint
);

A practical example:

CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE,
    country     TEXT DEFAULT 'US'
);

SQLite supports both column constraints and table constraints, so you can keep simple rules next to each column or define multi-column rules at the bottom of the statement.

If you have not created the database file yet, start with SQLite Create Database.

SQLite data types and type affinity

SQLite is more flexible than PostgreSQL, SQL Server, or MySQL. It uses type affinity, which means the declared type influences storage behavior, but SQLite still accepts a wider range of values than strictly typed engines.

SQLite's main storage classes are:

Common declarations you will see in real tables:

If you come from other databases, remember that declaring VARCHAR(50) in SQLite does not enforce the same length semantics you may expect elsewhere. When strict validation matters, pair type declarations with constraints.

Column and table constraints

Top-ranking SQLite tutorials usually stop after the syntax. Real projects need the constraint layer too.

Example with both column and table constraints:

CREATE TABLE IF NOT EXISTS orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    status       TEXT NOT NULL DEFAULT 'new',
    created_at   TEXT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

For a deeper look at edge cases such as multiple NULL values in UNIQUE columns or foreign key enforcement, read SQLite Constraints.

Create a table in the sqlite3 CLI

Open SQLite and create or connect to a database:

sqlite3 shop.db

Then run:

PRAGMA foreign_keys = ON;

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

CREATE TABLE IF NOT EXISTS orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    status       TEXT NOT NULL DEFAULT 'new',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Verify the tables:

.tables
.schema orders

If the table will be filtered or joined often, the next step is usually adding an index. Continue with SQLite Indexes.

Create a table from Python

SQLite is often created from code during app startup or test setup. Python makes that very easy:

import sqlite3

schema_sql = """
PRAGMA foreign_keys = ON;

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

CREATE TABLE IF NOT EXISTS orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER NOT NULL,
    total_cents  INTEGER NOT NULL CHECK (total_cents >= 0),
    status       TEXT NOT NULL DEFAULT 'new',
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
"""

with sqlite3.connect("shop.db") as connection:
    connection.executescript(schema_sql)

This pattern is useful for:

• project bootstrapping
• automated tests
• desktop applications shipping with a local SQLite file
• repeatable migrations for smaller projects

After running the script, open the same file in DbSchema to review the visual model, indexes, and relationships before sharing it with a team.

Useful CREATE TABLE patterns

CREATE TABLE IF NOT EXISTS

Use this in setup scripts so reruns do not fail if the table already exists.

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

TEMP or TEMPORARY tables

Temporary tables live only for the current connection:

CREATE TEMP TABLE recent_orders AS
SELECT *
FROM orders
WHERE status = 'new';

CREATE TABLE AS SELECT

You can create a table directly from a query result:

CREATE TABLE archived_orders AS
SELECT *
FROM orders
WHERE status = 'archived';

INTEGER PRIMARY KEY vs AUTOINCREMENT

Many pages overuse AUTOINCREMENT. In SQLite, INTEGER PRIMARY KEY already gives you automatic rowid behavior and is usually enough. Use AUTOINCREMENT only when you specifically need SQLite to avoid reusing old rowids, because it adds overhead.

Create tables visually in DbSchema

DbSchema is useful when you want more than raw DDL:

• diagram-based table design
• visual editing of columns, constraints, and relationships
• SQL generation before deployment
• schema documentation that non-developers can still understand

Typical DbSchema workflow

1. Connect to the SQLite database file.
2. Right-click in the diagram and choose Create Table.
3. Add columns and choose types.
4. Set PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK, or foreign keys.
5. Generate or synchronize the SQL.
6. Review the schema in the diagram and share it with the team.

Helpful DbSchema docs:

• Schema design
• Schema synchronization
• SQL Editor

Performance and design tips

Prefer precise constraints early

It is much easier to design tables correctly than to rebuild them later just to add missing rules.

Index join and filter columns separately from table creation

Keep the CREATE TABLE statement focused on structure, then add indexes based on actual queries. Read SQLite JOINs and SQLite Indexes next.

Use narrower schemas when possible

Avoid very wide tables if the data naturally belongs in separate entities. Simpler tables are easier to validate and query.

Consider WITHOUT ROWID only for specific designs

For some composite primary key tables, WITHOUT ROWID can help reduce storage and improve lookup behavior. It is useful, but only for specific patterns, not as a default.

FAQ

Does SQLite enforce data types strictly?

Not in the same way as strongly typed engines. SQLite uses type affinity, so constraints are often needed for stricter validation.

What is the difference between INTEGER PRIMARY KEY and AUTOINCREMENT?

INTEGER PRIMARY KEY already auto-generates rowids. AUTOINCREMENT is a stricter and slower variant that prevents reuse of old rowids.

Can I add a PRIMARY KEY or CHECK constraint later with ALTER TABLE?

Often not directly. Many constraint changes in SQLite require creating a new table, copying data, and renaming tables.

Should I create indexes inside CREATE TABLE?

Usually no. Create the table first, then add indexes based on the actual query workload. Continue with SQLite Indexes.

Conclusion

The SQLite CREATE TABLE statement is where schema quality starts. Good table design means choosing sensible column types, adding the right constraints, and keeping the structure aligned with how the application will query the data.

You can create tables quickly in the sqlite3 shell, automate them from Python, or design them visually in DbSchema when you need diagrams, generated SQL, schema docs, and easier review.

References

• SQLite CREATE TABLE documentation
• SQLite Datatypes documentation
• DbSchema Documentation