SQLite CREATE TABLE: Syntax, Data Types, Constraints, and Examples

A SQLite database file holds no tables until you run CREATE TABLE, and the statement reads exactly like the one you would write for PostgreSQL or MySQL:

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

SQLite treats one part of that statement differently: the declared type of a column is a recommendation about storage, not a rule the engine enforces on the values you insert. Constraints are the part SQLite does enforce, so they carry more of the weight here than in a server database.

SQLite CREATE TABLE syntax

Each column here carries a rule of its own:

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

A constraint written after a column applies to that column alone. A constraint written on its own line at the end of the list can name several columns, which is how you declare a composite primary key or a foreign key whose child side spans two columns.

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

SQLite data types and type affinity

SQLite stores every value in one of five storage classes, and the value carries its own type: "In SQLite, the datatype of a value is associated with the value itself, not with its container", as the datatypes documentation puts it.

The type you declare in CREATE TABLE sets the column's affinity, which is "the recommended type for data stored in that column". SQLite reads the declared type as text and applies these rules in the order shown, so an unfamiliar type name still lands somewhere sensible:

Affinity decides what SQLite tries to convert on the way in, and the length in parentheses is not part of the decision. SQLite ignores it: a column declared VARCHAR(50) accepts strings longer than 50 characters.

CREATE TABLE affinity_demo (
    label  VARCHAR(3),
    amount INTEGER
);

INSERT INTO affinity_demo VALUES ('abcdefgh', '42');

SELECT label, length(label), typeof(label), amount, typeof(amount)
FROM affinity_demo;

The eight-character string went into VARCHAR(3) untouched, because VARCHAR contains CHAR and therefore has TEXT affinity. The string '42' became an integer, because the column has INTEGER affinity and the text is a well-formed integer literal.

When you want the engine to reject the wrong type instead of converting it, declare the table STRICT. The STRICT tables documentation dates the feature to SQLite 3.37.0 (2021-11-27) and allows these type names in such a table: INT, INTEGER, REAL, TEXT, BLOB and ANY.

CREATE TABLE strict_demo (
    id     INTEGER PRIMARY KEY,
    amount INT
) STRICT;

INSERT INTO strict_demo VALUES (1, 'abc');

cannot store TEXT value in INT column strict_demo.amount

SQLite still applies the affinity rules first, and "if the value cannot be losslessly converted in the specified datatype, then an SQLITE_CONSTRAINT_DATATYPE error is raised". A STRICT table also tightens the primary key: its columns are implicitly NOT NULL.

Column and table constraints

SQLite has a short list of constraints, and it covers almost everything a table needs:

Every one of them except UNIQUE appears in this order table, the foreign key at the end of the list and the rest beside their columns:

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)
);

CHECK runs on write and not on read: "each time a new row is inserted into the table or an existing row is updated, the expression associated with each CHECK constraint is evaluated".

In SQLite a primary key does not imply NOT NULL. The CREATE TABLE documentation traces that to "a bug in some early versions" and says the behavior was documented rather than fixed, because a fix "might break legacy applications". SQLite rejects a NULL in a primary key column only when one of these holds:

• the column is INTEGER PRIMARY KEY
• the table is WITHOUT ROWID
• the table is STRICT
• the column is declared NOT NULL

A TEXT PRIMARY KEY therefore accepts as many NULL rows as you insert, so write NOT NULL beside it.

The foreign key is different again. SQLite parses it in either case, but enforcement is off by default and PRAGMA foreign_keys = ON has to be set separately on each connection. SQLite Constraints covers that switch, the multiple NULL values a UNIQUE column accepts, and the rest of the edge cases.

Create the tables from a shell or from Python

Open the file from the terminal:

sqlite3 shop.db

A shell session is one connection, so it turns foreign keys on before anything else:

PRAGMA foreign_keys = ON;

Then paste the customers and orders statements from the sections above, customers first, so the foreign key in orders has a table to point at. Neither prints anything, which is what success looks like in the shell. .tables lists what the file now contains:

sqlite> .tables
customers  orders

Save the same statements as schema.sql and an application can create the same schema at startup, or a test can create it once per run. Python's sqlite3 module runs the whole file in one call:

import sqlite3
from pathlib import Path

with sqlite3.connect("shop.db") as connection:
    connection.executescript(Path("schema.sql").read_text())

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

Useful CREATE TABLE patterns

CREATE TABLE IF NOT EXISTS

IF NOT EXISTS is what makes a setup script safe to run twice. With the clause in place, a second run over an existing table "simply has no effect (and no error message is returned)". Without it, the second run stops the script with an error.

TEMP or TEMPORARY tables

CREATE TEMP TABLE puts the new table in the temp database, which lives as long as the connection does. Nothing is written to the .db file, and another connection cannot see it:

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

CREATE TABLE AS SELECT

The same form without TEMP creates a permanent table from a query result:

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

It copies the column names and the values. The CREATE TABLE documentation is explicit about what the copy leaves behind:

• no primary key
• no constraints of any kind
• a default value of NULL on every column

Add those back yourself if the new table is going to be written to.

INTEGER PRIMARY KEY compared with AUTOINCREMENT

A column declared exactly INTEGER PRIMARY KEY is an alias for the row's internal rowid, so SQLite fills it in on every insert whether or not the AUTOINCREMENT keyword is present.

AUTOINCREMENT changes what those generated numbers promise: they are never reused after a delete and they always increase, which SQLite tracks in an internal table named sqlite_sequence. The price is stated on the AUTOINCREMENT page: the keyword "imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed." Write it only when a reused id would be a problem, for example when the ids appear in an audit trail.

Create tables visually in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

DbSchema draws each table as a box on a diagram and puts the same options you would type as DDL into dialogs:

1. Choose SQLite in Connect to Database and give the connection the path to your .db file. DbSchema reverse-engineers the tables, columns and foreign keys it finds into a diagram.
2. Right-click the diagram canvas and choose New Table.
3. Double-click the table header to open the Table Dialog.

The tabs of that dialog match the parts of the statement above, and the schema documentation shows each one:

• Columns, for names and data types
• Indexes, for the primary key and unique indexes
• Foreign Keys, for references to other tables
• Constraints, for check constraints such as total_cents >= 0
• Options, for the settings specific to the database

Where the change lands depends on how you are connected. On a live connection, DbSchema executes it against the SQLite file straight away. Disconnected, the new table stays in the .dbs design model until you open Schema → Synchronize Model with Database, read the generated script, and click Execute. Statements you would rather write by hand go into the SQL Editor.

Performance and design tips

Getting the constraints right in the first statement is worth the extra minutes, because SQLite's ALTER TABLE is deliberately small. It covers these alterations and no others:

• rename the table
• rename a column
• add a column
• drop a column
• set or drop a column's NOT NULL constraint, which the ALTER TABLE documentation dates to SQLite 3.53.0 (2026-04-09)

Adding a PRIMARY KEY, a UNIQUE constraint or a CHECK is not on that list. Those go through the rebuild the same page spells out: create a new table in the shape you want, copy the rows into it, drop the old table, and rename the new one into its place, all inside a transaction.

The indexes you need most are already in the statement: "in most cases, UNIQUE and PRIMARY KEY constraints are implemented by creating a unique index in the database", the exceptions being INTEGER PRIMARY KEY and primary keys on WITHOUT ROWID tables. Any other index is a separate CREATE INDEX, written once you know which columns the queries filter and join on. SQLite JOINs and SQLite Indexes cover that step.

WITHOUT ROWID is the one structural option worth knowing before you write the statement, because it changes what the primary key is rather than how it is indexed. Such a table must have a PRIMARY KEY, and SQLite raises an error when the clause appears without one. The WITHOUT ROWID documentation aims it at "tables that have non-integer or composite (multi-column) PRIMARY KEYs and that do not store large strings or BLOBs", where it "can use about half the amount of disk space and can operate nearly twice as fast". A table keyed by a single INTEGER PRIMARY KEY runs faster as an ordinary rowid table, so leave those alone.

Write the columns, then spend the extra minute on the constraints, because those are the rules SQLite enforces and the ones that are awkward to add later. To see the tables you just created as a diagram, download DbSchema at https://dbschema.com/download.html and connect it to your .db file: connecting, reverse engineering, the diagrams and the SQL Editor are in the free Community Edition, while saving the design to a .dbs file and schema synchronization are in Pro.

Sources

1. SQLite documentation: CREATE TABLE
2. SQLite documentation: Datatypes In SQLite
3. SQLite documentation: STRICT Tables
4. SQLite documentation: SQLite Foreign Key Support
5. SQLite documentation: SQLite Autoincrement
6. SQLite documentation: ALTER TABLE
7. SQLite documentation: The WITHOUT ROWID Optimization