SQLite: table … already exists

What it means

CREATE TABLE names something the database already has, so SQLite refuses while preparing the statement: SQLITE_ERROR (code 1), table <name> already exists, and nothing is created.

Tables, views and indexes share one set of names in each database, and the message names whatever already holds it. Creating a table called v_orders when a view has that name gives view v_orders already exists. The same check gives:

index orders_customer already exists and trigger t1 already exists for CREATE INDEX and CREATE TRIGGER;
there is already an index named orders_customer for a table with an index’s name, and there is already a table named customers the other way round;
there is already another table or index with this name: customers for ALTER TABLE … RENAME TO.

Names aren’t case-sensitive: CREATE TABLE Customers collides with customers.

Common causes

The schema script or migration ran twice. A schema.sql run against a database that already has the tables, a setup step that runs on every start, or a migration tool whose record of applied migrations (Django’s django_migrations, Alembic’s alembic_version) doesn’t match the file, because the tables were made another way or the record was lost.
A failed migration left its temporary table. SQLite can’t change most things about a column in place, so tools rebuild the table: create a new one under a temporary name, copy the rows, drop the old one, rename the new one. Alembic’s batch mode calls it _alembic_tmp_<table> and Django calls it new__<table>. If the run stops partway, that table stays and the next run fails with table _alembic_tmp_users already exists.
A view or index has the name. You’re creating a table where a view or index of that name exists.
The names differ only in case, such as Customers and customers.

How to fix it

Say IF NOT EXISTS when “create it unless it’s there” is what you mean

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

CREATE INDEX, CREATE VIEW and CREATE TRIGGER take IF NOT EXISTS too. It doesn’t compare definitions: if the existing table has different columns, it keeps the old one silently, and the difference shows up later as no such column. For a schema that changes, use migrations.

Look at what’s there

SELECT type, name, tbl_name, sql FROM sqlite_schema WHERE name = 'customers' COLLATE NOCASE;

type says whether it’s a table, view, index or trigger, and sql shows how it was created. If it’s the table you were about to create, the script doesn’t need to create it.

Bring the migration record in line with the file

If the tables are already exactly what a migration would create, mark that migration as applied instead of running it: python manage.py migrate --fake <app> <migration> in Django, alembic stamp <revision> in Alembic. Check the columns first (.schema <table> in the shell): faking a migration that didn’t really run leaves the file and the record disagreeing the other way.

Drop a leftover temporary table

After a failed rebuild, the original table normally still has its rows. Compare before you drop anything:

SELECT count(*) FROM users;
SELECT count(*) FROM _alembic_tmp_users;

When the original is intact, drop the leftover and run the migration again:

DROP TABLE _alembic_tmp_users;

If the original is gone and only the temporary table has the rows, rename it back (ALTER TABLE _alembic_tmp_users RENAME TO users;) instead of dropping it.

Choose another name

If the existing view, index or table is something else that needs its name, give the new object a different one. Drop the old object only when you’re sure nothing uses it.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, on shop.db, which has customers and orders tables:

sqlite3 shop.db "CREATE TABLE customers (id INTEGER PRIMARY KEY);"

Error: in prepare, table customers already exists
  CREATE TABLE customers (id INTEGER PRIMARY KEY);
               ^--- error here

The shell adds “Error: in prepare,” and points at the name; SQLite’s message is table customers already exists. The same statement with IF NOT EXISTS succeeded and changed nothing. The other forms:

Error: in prepare, table Customers already exists
Error: in prepare, view v_orders already exists
Error: in prepare, index orders_customer already exists
Error: in prepare, trigger t1 already exists
Error: in prepare, there is already an index named orders_customer
Error: in prepare, there is already a table named customers
Error: in prepare, there is already another table or index with this name: customers

The last three came from CREATE TABLE orders_customer, CREATE INDEX customers ON orders(total) and ALTER TABLE orders RENAME TO customers. A CREATE TEMP TABLE customers succeeded: temporary tables live in their own temp database.

In Inlet

⌘P opens a table by name from the ones in the file, and the query editor completes table names from the live schema, so you can see what’s already there before you create anything. On protected connections, a DROP TABLE typed in the query editor asks before it runs and says why.

table … already exists