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

CREATE TABLE adds a table, with its columns and the rules for each row, to a SQLite database file. For example, you can create a products table with a name and a price for each product.

A new database file stays empty until you create a table. CREATE TABLE in SQLite has the same form as in other databases, but SQLite treats column types as preferences.

The basic CREATE TABLE has this shape:

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

• IF NOT EXISTS skips the statement when a table of that name already exists.
• table_name names the new table.
• column_name type declares a column and its type, such as name TEXT.
• column_constraint is a rule on one column: PRIMARY KEY, NOT NULL, DEFAULT, UNIQUE, CHECK or REFERENCES.
• table_constraint is a rule over one or more columns, written after the last column. An example is a primary key over two columns: PRIMARY KEY (column_a, column_b).
• STRICT makes SQLite reject a value that doesn't match the column's type.

Only the table name and at least one column are required.

For UNIQUE, CHECK, and the constraints that ALTER TABLE can add to an existing table, read SQLite Constraints.

Open a database file in the sqlite3 shell, SQLite's command-line program. The shell creates the file if the file doesn't exist:

sqlite3 shop.db

Then create the sample table, products, and add two rows:

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  price REAL,
  stock INTEGER DEFAULT 0
);

INSERT INTO products (name, price) VALUES
  ('Lamp', 24.5),
  ('Desk', 180);

.schema products prints the CREATE TABLE that the file stores.

Run the examples in order. Several of them add or delete rows. Every result and error below comes from SQLite 3.50.4 through Python's sqlite3 module.

PRIMARY KEY

A PRIMARY KEY identifies each row by a value that no two rows share. For example, id identifies each product. A column declared exactly INTEGER PRIMARY KEY also numbers new rows by itself.

SELECT id, name FROM products;

The INSERT gave no id, so SQLite filled in 1 and 2. A new row gets one more than the largest id in the table.

Spell the type as INTEGER: an INT PRIMARY KEY doesn't number new rows (see "What goes wrong").

NOT NULL

NOT NULL rejects a row that has no value in the column. For example, NOT NULL on name rejects a product without a name.

INSERT INTO products (price) VALUES (12);

NOT NULL constraint failed: products.name

The INSERT gave no name, and name has no DEFAULT. SQLite rejected the whole row, and the message names the table and the column.

DEFAULT

DEFAULT sets the value that a column gets when an INSERT leaves the column out. For example, stock has DEFAULT 0, so a new product starts with 0 in stock.

INSERT INTO products (name, price, stock) VALUES ('Chair', 45, 8);

SELECT name, price, stock FROM products;

Lamp and Desk got 0 from the DEFAULT, because their INSERT named no stock. Chair has the 8 that its INSERT gave.

Data types and type affinity

In SQLite, a column's declared type is only a preference, called its affinity. SQLite converts a value to that type where possible, and otherwise stores the value unchanged. For example, the next INSERT puts text into price and stock.

INSERT INTO products (name, price, stock) VALUES ('Stool', '19.90', 'five');

SELECT name, price, typeof(price), stock, typeof(stock) FROM products;

typeof() returns the type of the stored value. Stool's price came in as the text '19.90', and the REAL column stored the number 19.9. Desk's 180 became 180.0 the same way.

Stool's stock, 'five', isn't a number, so the INTEGER column stores the text unchanged. A program that reads stock as a number now gets text.

SQLite picks the affinity from the name of the declared type. SQLite checks the rows below in order, and the first match decides (datatypes documentation):

SQLite ignores the length in a type such as VARCHAR(5), so a VARCHAR(5) column stores a ten-character string in full.

SQLite has no date type and no boolean type. Store a date as TEXT, such as '2026-11-11', and a boolean as the integer 0 or 1.

STRICT

STRICT after the closing parenthesis makes SQLite reject a value that SQLite can't convert to the column's type. For example, a STRICT copy of products rejects the text 'five' in stock. STRICT needs SQLite 3.37.0 or later.

CREATE TABLE products_strict (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  price REAL,
  stock INTEGER DEFAULT 0
) STRICT;

INSERT INTO products_strict (name, price, stock) VALUES ('Stool', '19.90', 'five');

cannot store TEXT value in INTEGER column products_strict.stock

The price '19.90' alone would have passed, because SQLite can convert it to a REAL. The text 'five' can't become an INTEGER, so SQLite rejected the row.

A STRICT table accepts only the type names INT, INTEGER, REAL, TEXT, BLOB and ANY, so creating a STRICT table with a VARCHAR(20) column fails with an "unknown datatype" error.

FOREIGN KEY

A FOREIGN KEY accepts a value only if the same value exists in another table. For example, an order can name only an existing product. Create a second table, orders, whose product_id points at products:

PRAGMA foreign_keys = ON;

CREATE TABLE orders (
  id         INTEGER PRIMARY KEY,
  product_id INTEGER,
  quantity   INTEGER,
  FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO orders (product_id, quantity) VALUES (9, 1);

FOREIGN KEY constraint failed

No product has id 9, so SQLite rejected the order. A PRAGMA is a SQLite command that reads or changes a setting. The PRAGMA on the first line turned the check on, because foreign keys are off by default.

On one column, you can also write the same rule as product_id INTEGER REFERENCES products (id).

IF NOT EXISTS

IF NOT EXISTS skips the CREATE TABLE when a table of that name already exists. For example, a setup script with IF NOT EXISTS can run more than once. Without the clause, a second run fails on every table that already exists:

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

table products already exists

With CREATE TABLE IF NOT EXISTS products, the statement does nothing and returns no error. IF NOT EXISTS compares only the table name, so products keeps its four columns.

AUTOINCREMENT

AUTOINCREMENT after INTEGER PRIMARY KEY stops SQLite from giving a new row the id of a deleted row. Without AUTOINCREMENT, deleting the row with the largest id frees that id. For example, delete Stool, which has the largest id, and add a product:

DELETE FROM products WHERE name = 'Stool';

INSERT INTO products (name, price) VALUES ('Shelf', 60);

SELECT id, name FROM products;

Shelf got id 4, which was Stool's id before the DELETE. With id INTEGER PRIMARY KEY AUTOINCREMENT, the same statements give Shelf id 5.

Use AUTOINCREMENT only where an old id must never be reused for a new row, such as an id stored outside the database. The keyword costs extra CPU, memory, disk space and disk I/O.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT creates a table with the columns and the rows of a query's result. For example, copy the products under 100 into a new table:

CREATE TABLE cheap_products AS
SELECT id, name, price FROM products WHERE price < 100;

SELECT id, name, price FROM cheap_products;

The new table has the three columns of the SELECT and the three rows that matched. Desk, at 180.0, isn't there.

The copy has no PRIMARY KEY, no constraints and no DEFAULT. SQLite declared the copy's id column as INT, so a new row that leaves out the id gets NULL.

For a copy that only your connection sees, write CREATE TEMP TABLE. SQLite deletes a TEMP table when the connection closes.

What goes wrong

Foreign keys are off on a new connection

Foreign keys are off on every new connection, such as a second sqlite3 shell on shop.db. There, PRAGMA foreign_keys returns 0:

PRAGMA foreign_keys;

Add the order for product 9 again:

INSERT INTO orders (product_id, quantity) VALUES (9, 1);

SELECT id, product_id, quantity FROM orders;

SQLite accepted the order. Turn foreign keys on in every connection that your application opens. In Python, run the PRAGMA after each sqlite3.connect():

import sqlite3

connection = sqlite3.connect("shop.db")
connection.execute("PRAGMA foreign_keys = ON")

An INT PRIMARY KEY accepts NULL

With INT in place of INTEGER, the primary key is an ordinary column, and an INSERT without an id stores NULL. Create a separate table, items, with an INT PRIMARY KEY:

CREATE TABLE items (
  id   INT PRIMARY KEY,
  name TEXT
);

INSERT INTO items (name) VALUES ('Lamp'), ('Desk');

SELECT id, name FROM items;

The CREATE TABLE documentation calls NULL in a primary key a bug of early versions. SQLite keeps the bug so that old applications keep working.

To number the rows, write INTEGER. To reject the NULL, add NOT NULL to the id column, or make the table STRICT.

Create the table in DbSchema

DbSchema connects to a SQLite file, such as shop.db, and draws products and orders on a diagram, with the foreign key as a line between them.

To create a table in DbSchema, click an empty spot on the diagram and choose Table → Create Table. Type the name, then add each column in the Columns tab, as Create a table shows.

Press SQL at the bottom of the dialog to read the CREATE TABLE that DbSchema writes. While DbSchema is connected, OK runs that CREATE TABLE in the file.

Download DbSchema at https://dbschema.com/download.html, connect it to your .db file, and paste this article's statements into the SQL Editor. The free Community Edition includes the connection, the diagram with Create Table, and the SQL Editor.

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Sources

1. CREATE TABLE
2. Datatypes In SQLite
3. STRICT Tables
4. SQLite Foreign Key Support
5. SQLite Autoincrement
6. Temporary Files Used By SQLite
7. Command Line Shell For SQLite