SQLite CREATE TABLE IF NOT EXISTS: Syntax, Examples and Gotchas (Tested)

Ninad Pathak

October 7, 2026

Running the same CREATE TABLE twice stops with an error like table users already exists, so a setup script that runs on every start needs table creation that’s safe to repeat. CREATE TABLE IF NOT EXISTS is that, and in this article I’ll walk you through how it behaves, including the one thing it won’t do for you.

TLDR

CREATE TABLE IF NOT EXISTS users (...) creates the table if it’s missing and does nothing, without an error, if it’s there.
It never changes an existing table. A new column in the statement is ignored if the table already exists.

Other CREATE statements, such as CREATE INDEX, take IF NOT EXISTS too. ALTER TABLE ... ADD COLUMN doesn’t.
To insert a row only if it’s missing, use INSERT OR IGNORE or INSERT ... WHERE NOT EXISTS.

What does CREATE TABLE IF NOT EXISTS do?

It creates a table only when no table or view with that name exists. SQLite’s documentation says that if a table or view of the same name already exists, the command has “no effect (and no error message is returned)” (SQLite CREATE TABLE).

That makes it a natural first line for code that runs more than once, like a setup script or a migration. The same script works on a new database and on one that’s already set up.

CREATE TABLE IF NOT EXISTS syntax

CREATE TABLE IF NOT EXISTS [schema_name.]table_name (
  column_name type [constraints],
  ...
  [table_constraints]
);

Everything after the table name is the same as a plain CREATE TABLE. See SQLite CREATE TABLE for column types and constraints.

How to create a table only if it doesn’t exist

To show you the difference, I will create a users table, then try to create it again with and without the clause.

Step 1: Create the table

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
sqlite> INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');

Step 2: Run plain CREATE TABLE again

Without the clause, the second run fails:

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
Error: in prepare, table users already exists
  CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNI
               ^--- error here

Step 3: Run it with IF NOT EXISTS

Add the clause and the second run succeeds without changing anything. The rows are still there:

sqlite> CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
sqlite> SELECT * FROM users;
id  name   email
--  -----  -----------------
1   Alice  alice@example.com
2   Bob    bob@example.com

Examples

The examples continue with the same users table.

A nightly script that’s safe to re-run

Say a script builds a daily summary from raw events every night. IF NOT EXISTS creates the summary table on the first run only, and INSERT OR REPLACE with a primary key on (day, event_type) keeps a re-run from duplicating rows:

-- Safe to run every day: the table is created on the first run only.
CREATE TABLE IF NOT EXISTS daily_events (
  day        TEXT    NOT NULL,
  event_type TEXT    NOT NULL,
  n          INTEGER NOT NULL,
  PRIMARY KEY (day, event_type)
);

INSERT OR REPLACE INTO daily_events (day, event_type, n)
SELECT date(ts), event_type, count(*)
FROM raw_events
WHERE date(ts) = '2026-10-06'
GROUP BY 1, 2;

SELECT * FROM daily_events ORDER BY day, event_type;

I ran it twice against the same database. The second run gives the same result:

$ sqlite3 -header -column t.db < daily_summary.sql
day         event_type  n
----------  ----------  -
2026-10-06  search      2
2026-10-06  signup      1
$ sqlite3 -header -column t.db < daily_summary.sql
day         event_type  n
----------  ----------  -
2026-10-06  search      2
2026-10-06  signup      1

Create a table if it doesn’t exist from Python

The statement is the same from Python’s sqlite3 module. This script can run on every start:

import sqlite3

con = sqlite3.connect("app.db")
con.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
""")
con.commit()
tables = [r[0] for r in con.execute("SELECT name FROM sqlite_schema WHERE type = 'table'")]
print("tables:", tables)
con.close()

$ python3 setup_py.py
tables: ['users']
$ python3 setup_py.py
tables: ['users']

IF NOT EXISTS for indexes, views and triggers

Other CREATE statements accept IF NOT EXISTS too:

sqlite> CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
sqlite> CREATE VIEW IF NOT EXISTS user_emails AS SELECT name, email FROM users;
sqlite> CREATE TRIGGER IF NOT EXISTS users_lower_email AFTER INSERT ON users BEGIN UPDATE users SET email = lower(email) WHERE id = NEW.id; END;
sqlite> SELECT type, name FROM sqlite_schema WHERE name <> 'users' AND name NOT LIKE 'sqlite_%' ORDER BY type, name;
type     name
-------  -----------------
index    idx_users_name
trigger  users_lower_email
view     user_emails

Check whether a table exists

To branch on whether a table exists, query sqlite_schema (also known as sqlite_master):

sqlite> SELECT count(*) AS users_exists FROM sqlite_schema WHERE type = 'table' AND name = 'users';
users_exists
------------
1

IF NOT EXISTS makes this check unnecessary when all you want is to create the table.

Edge cases

Each of these is a case where IF NOT EXISTS doesn’t do what people expect.

It doesn’t change an existing table

This statement adds a phone column, but users already exists, so SQLite ignores the whole statement. The table keeps its old columns:

sqlite> CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, phone TEXT);
sqlite> SELECT name FROM pragma_table_info('users');
name
-----
id
name
email

To change an existing table, use ALTER TABLE. See SQLite ALTER TABLE ADD COLUMN.

There’s no ADD COLUMN IF NOT EXISTS

ALTER TABLE has no IF NOT EXISTS, and adding a column twice is an error. Check pragma_table_info first, and only add the column when the count is 0:

sqlite> ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT;
Error: in prepare, near "EXISTS": syntax error
  ALTER TABLE users ADD COLUMN IF NOT EXISTS phone TEXT;
                        error here ---^
sqlite> SELECT count(*) AS has_phone FROM pragma_table_info('users') WHERE name = 'phone';
has_phone
---------
0
sqlite> ALTER TABLE users ADD COLUMN phone TEXT;
sqlite> ALTER TABLE users ADD COLUMN phone TEXT;
Error: in prepare, duplicate column name: phone

An index with the same name still causes an error

The docs note that an index with the same name still causes an error, even with IF NOT EXISTS:

sqlite> CREATE INDEX reports ON users(name);
sqlite> CREATE TABLE IF NOT EXISTS reports (id INTEGER PRIMARY KEY);
Error: in prepare, there is already an index named reports

Insert a row only if it doesn’t exist

IF NOT EXISTS works on tables, not rows. For rows, INSERT OR IGNORE skips a row that breaks a UNIQUE constraint, and WHERE NOT EXISTS checks first:

sqlite> INSERT OR IGNORE INTO users (name, email) VALUES ('Alice', 'alice@example.com'); SELECT changes();
changes()
---------
0
sqlite> INSERT INTO users (name, email) SELECT 'Carol', 'carol@example.com' WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'carol@example.com'); SELECT changes();
changes()
---------
1

To update the existing row instead, use an upsert.

The opposite: DROP TABLE IF EXISTS

DROP TABLE IF EXISTS removes a table only if it’s there. Without the clause, a missing table is an error:

sqlite> DROP TABLE IF EXISTS old_users;
sqlite> DROP TABLE old_users;
Error: in prepare, no such table: old_users

SQL Server has no CREATE TABLE IF NOT EXISTS

SQL Server’s CREATE TABLE doesn’t take this clause. Instead, check for the table with OBJECT_ID first, the way Microsoft’s docs check for a table before dropping it (Microsoft Learn: OBJECT_ID). SQL Server syntax, not executed:

IF OBJECT_ID(N'dbo.users', N'U') IS NULL
    CREATE TABLE dbo.users (id INT IDENTITY PRIMARY KEY, name NVARCHAR(100) NOT NULL);

Conclusion

Use CREATE TABLE IF NOT EXISTS in any script that may run more than once. Remember that it only creates. It won’t add columns to a table that’s already there, so pair it with ALTER TABLE when your schema changes. For rows, use INSERT OR IGNORE or an upsert.