SQLite CREATE TABLE: Syntax, Constraints and Examples

Ninad Pathak

October 7, 2026

CREATE TABLE in SQLite looks like the one in other databases, with a couple of differences that matter: ordinary tables get a hidden rowid, and foreign keys are off by default. In this article I’ll walk you through the syntax and constraints with examples I’ve run, including those SQLite-only parts.

TLDR

CREATE TABLE name (column TYPE constraints, ...); creates a table.
SQLite supports the usual constraints, such as NOT NULL and CHECK.

A column declared exactly INTEGER PRIMARY KEY becomes the table’s rowid and fills itself in.
Add IF NOT EXISTS to skip the statement when the table already exists.
Run PRAGMA foreign_keys = ON; on each connection, or REFERENCES isn’t checked.

What is CREATE TABLE in SQLite?

CREATE TABLE defines a new table’s columns and the rules its rows must follow. Here’s a complete example with an auto-numbered key and a default timestamp:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, created TEXT DEFAULT CURRENT_TIMESTAMP);
sqlite> INSERT INTO users (name) VALUES ('ada'); SELECT id, name, length(created) AS created_len FROM users;
id  name  created_len
--  ----  -----------
1   ada   19

SQLite CREATE TABLE syntax

The general form, from the CREATE TABLE reference:

CREATE [TEMP] TABLE [IF NOT EXISTS] table_name (
  column_name type [column_constraint ...],
  ...
  [, table_constraint ...]
) [WITHOUT ROWID] [, STRICT];

Constraint	What it does
`PRIMARY KEY`	Identifies each row
`NOT NULL`	Rejects NULL
`UNIQUE`	Rejects duplicates
`DEFAULT value`	Fills in a value when the insert leaves the column out
`CHECK (expr)`	Rejects rows where the expression is false
`REFERENCES other(column)`	A foreign key (checked only when foreign keys are on)

Column types are flexible in SQLite. See SQLite data types and STRICT tables.

How to create a table in SQLite

To show you how this works, I will create two tables for a small blog, authors and posts.

Step 1: Define the columns and constraints

DEFAULT (date('now')) fills in today’s date. A default can be a constant or “any constant expression enclosed in parentheses” (The DEFAULT clause):

sqlite> CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, email TEXT, joined TEXT NOT NULL DEFAULT (date('now')));
sqlite> CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER NOT NULL REFERENCES authors(id), title TEXT NOT NULL, words INTEGER NOT NULL DEFAULT 0 CHECK (words >= 0));
sqlite> .schema posts
CREATE TABLE posts (id INTEGER PRIMARY KEY, author_id INTEGER NOT NULL REFERENCES authors(id), title TEXT NOT NULL, words INTEGER NOT NULL DEFAULT 0 CHECK (words >= 0));

Step 2: Insert rows and let INTEGER PRIMARY KEY number them

If a table’s primary key is a single column declared exactly INTEGER, “then the column becomes an alias for the rowid” (ROWIDs and the INTEGER PRIMARY KEY). It fills itself in on insert. Selecting rowid returns the same column, which is why the shell labels it id:

sqlite> INSERT INTO authors (name, email) VALUES ('ada', 'ada@example.com'), ('linus', NULL);
sqlite> SELECT rowid, id, name, joined = date('now') AS joined_today FROM authors;
id  id  name   joined_today
--  --  -----  ------------
1   1   ada    1
2   2   linus  1

The spelling matters: “A PRIMARY KEY column only becomes an integer primary key if the declared type name is exactly ‘INTEGER’.” With INT or BIGINT, the primary key is an ordinary column.

Step 3: Check that the constraints work

With ada already in the table, each constraint rejects a bad row with its own error:

sqlite> INSERT INTO authors (name) VALUES ('ada');
Error: stepping, UNIQUE constraint failed: authors.name (19)
sqlite> INSERT INTO authors (email) VALUES ('x@example.com');
Error: stepping, NOT NULL constraint failed: authors.name (19)
sqlite> INSERT INTO posts (author_id, title, words) VALUES (1, 'Draft', -5);
Error: stepping, CHECK constraint failed: words >= 0 (19)

Step 4: Make the script safe to re-run with IF NOT EXISTS

Without IF NOT EXISTS, creating the table again is an error. With it, the statement does nothing and reports no error:

sqlite> CREATE TABLE authors (id INTEGER PRIMARY KEY);
Error: in prepare, table authors already exists
  CREATE TABLE authors (id INTEGER PRIMARY KEY);
               ^--- error here
sqlite> CREATE TABLE IF NOT EXISTS authors (id INTEGER PRIMARY KEY);

Examples

Each example below shows a different kind of table.

Create a table from a query

CREATE TABLE … AS SELECT creates and fills a table in one statement. “A table created using CREATE TABLE AS has no PRIMARY KEY and no constraints of any kind” (CREATE TABLE … AS SELECT). Its column types come from the query’s expression affinity, here INT and TEXT:

sqlite> CREATE TABLE author_names AS SELECT id, name FROM authors;
sqlite> .schema author_names
CREATE TABLE author_names(id INT,name TEXT);
sqlite> SELECT * FROM author_names;
id  name
--  -----
1   ada
2   linus

Temporary tables

CREATE TEMP TABLE makes a table that belongs to the current connection and disappears when it closes. Each line below is a separate connection, so the second can’t see the table:

sqlite> CREATE TEMP TABLE scratch (x); INSERT INTO scratch VALUES (1); SELECT count(*) FROM scratch;
count(*)
--------
1
sqlite> SELECT count(*) FROM scratch;
Error: in prepare, no such table: scratch

WITHOUT ROWID tables

A WITHOUT ROWID table stores rows by their primary key and has no rowid column. “The PRIMARY KEY is optional for ordinary tables but is required for WITHOUT ROWID tables” (CREATE TABLE):

sqlite> CREATE TABLE settings (key TEXT PRIMARY KEY, value TEXT) WITHOUT ROWID;
sqlite> INSERT INTO settings VALUES ('theme', 'dark');
sqlite> SELECT rowid, key, value FROM settings;
Error: in prepare, no such column: rowid
  SELECT rowid, key, value FROM settings;
         ^--- error here
sqlite> SELECT key, value FROM settings;
key    value
-----  -----
theme  dark

Edge cases

SQLite has two defaults that surprise people coming from other databases.

Foreign keys are off by default

“Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection” (Enabling foreign key support). Without the pragma, a REFERENCES clause isn’t checked:

sqlite> INSERT INTO posts (author_id, title) VALUES (99, 'Orphan');
sqlite> SELECT id, author_id, title FROM posts;
id  author_id  title
--  ---------  ------
1   99         Orphan

With PRAGMA foreign_keys = ON on the connection, the same kind of insert fails:

sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO posts (author_id, title) VALUES (98, 'Orphan 2');
Runtime error near line 2: FOREIGN KEY constraint failed (19)

A PRIMARY KEY column can hold NULL

The docs call this a bug that stays for compatibility. Unless the column is declared NOT NULL or is an INTEGER PRIMARY KEY, “SQLite allows NULL values in a PRIMARY KEY column” (CREATE TABLE). WITHOUT ROWID and STRICT tables are exceptions too. Both NULL keys go in without complaint:

sqlite> CREATE TABLE codes (code TEXT PRIMARY KEY, label TEXT);
sqlite> INSERT INTO codes VALUES (NULL, 'first'), (NULL, 'second');
sqlite> SELECT code, label FROM codes;
code  label
----  ------
      first
      second

Add NOT NULL to a text primary key to close that gap.

Conclusion

CREATE TABLE in SQLite works like in other databases, with a few differences. Use INTEGER PRIMARY KEY for an auto-numbered key, and add NOT NULL to other primary keys. Turn foreign keys on for every connection, and add IF NOT EXISTS to setup scripts.