SQLite DROP TABLE: How to Delete a Table (IF EXISTS, Foreign Keys, All Tables)

Ninad Pathak

October 7, 2026

SQLite’s DROP TABLE removes one table per statement, so the MySQL form that lists several tables stops at the comma. Foreign keys can block a drop too. In this article I’ll walk you through dropping tables safely, including how to drop every table at once.

TLDR

DROP TABLE contacts; deletes the table and every row in it. Its indexes and triggers go too, and once it’s committed there’s no undo.
DROP TABLE IF EXISTS contacts; does nothing, instead of failing, when the table isn’t there.

Views that use the table are not dropped. They fail the next time you query them.
With PRAGMA foreign_keys = ON, you can’t drop a parent table while child rows point at it. Drop the child table first.
The file keeps its size until you run VACUUM. To delete a whole database, delete its file.

What is DROP TABLE in SQLite?

DROP TABLE removes a table and everything in it. SQLite’s docs: “The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted” (SQLite DROP TABLE). To remove rows but keep the table, use DELETE.

SQLite DROP TABLE syntax

DROP TABLE [IF EXISTS] [schema_name.]table_name;

Part	What it does
`IF EXISTS`	Skips quietly if the table doesn’t exist
`schema_name.`	`main`, `temp`, or the name of an attached database
`table_name`	One table. A comma-separated list isn’t allowed

How to drop a table in SQLite

To show you what DROP TABLE removes, I will use a small database with four tables. The contacts table also has an index and a trigger, and a view reads from it:

CREATE TABLE contacts (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, contact_id INTEGER NOT NULL REFERENCES contacts(id), total REAL);
CREATE TABLE logs (id INTEGER PRIMARY KEY, msg TEXT);
CREATE TABLE tmp_import (line TEXT);
CREATE UNIQUE INDEX idx_contacts_email ON contacts(email);
CREATE TRIGGER trg_contacts_log AFTER INSERT ON contacts BEGIN INSERT INTO logs (msg) VALUES ('new contact'); END;
CREATE VIEW contact_names AS SELECT name FROM contacts;
INSERT INTO contacts (name, email) VALUES ('Ada', 'ada@example.com'), ('Linus', 'linus@example.com');
INSERT INTO orders (contact_id, total) VALUES (1, 20.0);

sqlite> SELECT type, name, tbl_name FROM sqlite_schema ORDER BY type, name;
type     name                tbl_name
-------  ------------------  -------------
index    idx_contacts_email  contacts
table    contacts            contacts
table    logs                logs
table    orders              orders
table    tmp_import          tmp_import
trigger  trg_contacts_log    contacts
view     contact_names       contact_names

Step 1: Drop the table

sqlite> DROP TABLE tmp_import;
sqlite> SELECT * FROM tmp_import;
Error: in prepare, no such table: tmp_import

Step 2: Guard it with IF EXISTS

Dropping it again fails:

sqlite> DROP TABLE tmp_import;
Error: in prepare, no such table: tmp_import

With IF EXISTS, the same statement succeeds and does nothing:

sqlite> DROP TABLE IF EXISTS tmp_import;

Examples

Each example starts from a fresh copy of the same database.

What gets dropped with the table

Dropping contacts (with foreign keys off) also removes its index and trigger. The view stays, and fails when you query it:

sqlite> PRAGMA foreign_keys = OFF; DROP TABLE contacts;
sqlite> SELECT type, name FROM sqlite_schema ORDER BY type, name;
type   name
-----  -------------
table  logs
table  orders
table  tmp_import
view   contact_names
sqlite> SELECT * FROM contact_names;
Error: in prepare, no such table: main.contacts

Drop or rewrite views that depend on a table before you drop it. See SQLite views.

Drop a table that other tables reference

With foreign keys on, SQLite runs an implicit DELETE FROM first. If that would leave child rows pointing at nothing, the drop fails and “the table is not dropped” (SQLite DROP TABLE):

sqlite> PRAGMA foreign_keys = ON; DROP TABLE contacts;
Error: stepping, FOREIGN KEY constraint failed (19)

Drop the child table first, then the parent:

sqlite> PRAGMA foreign_keys = ON; DROP TABLE orders; DROP TABLE contacts;
sqlite> SELECT name FROM sqlite_schema WHERE type = 'table';
name
----------
logs
tmp_import

If the child table’s foreign key has ON DELETE CASCADE, dropping the parent doesn’t fail. The implicit DELETE FROM deletes the child rows instead (SQLite DROP TABLE).

Drop multiple tables

SQLite takes one table per statement:

sqlite> DROP TABLE logs, tmp_import;
Error: in prepare, near ",": syntax error
  DROP TABLE logs, tmp_import;
                 ^--- error here

Write one DROP TABLE for each table, separated by semicolons.

Drop all tables

Generate the statements from sqlite_schema. %w quotes each name safely, and the filter skips SQLite’s own internal tables:

sqlite> SELECT printf('DROP TABLE IF EXISTS "%w";', name) AS stmt FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite\_%' ESCAPE '\';
stmt
----------------------------------
DROP TABLE IF EXISTS "contacts";
DROP TABLE IF EXISTS "orders";
DROP TABLE IF EXISTS "logs";
DROP TABLE IF EXISTS "tmp_import";

Then run them with foreign keys off, dropping views first:

$ sqlite3 shop.db "PRAGMA foreign_keys = OFF; DROP VIEW IF EXISTS contact_names; DROP TABLE IF EXISTS contacts; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS logs; DROP TABLE IF EXISTS tmp_import;"
sqlite> SELECT count(*) AS objects_left FROM sqlite_schema;
objects_left
------------
0

If you want an empty database anyway, deleting the file is quicker (see below).

Drop an index

DROP INDEX works the same way, with IF EXISTS too:

sqlite> DROP INDEX idx_contacts_email;
sqlite> SELECT name FROM sqlite_schema WHERE type = 'index';

Drop a temporary table

Temp tables live in the temp schema and vanish when the connection closes. You can still drop one early:

sqlite> CREATE TEMP TABLE big_orders AS SELECT * FROM orders WHERE total > 10; SELECT count(*) AS rows_in_temp FROM big_orders; DROP TABLE temp.big_orders;
rows_in_temp
------------
1

Drop a table in an attached database

Prefix the table with the database’s alias. See SQLite attach database:

sqlite> ATTACH DATABASE 'archive.db' AS archive; DROP TABLE archive.old_logs; SELECT count(*) AS tables_in_archive FROM archive.sqlite_schema WHERE type = 'table';
tables_in_archive
-----------------
0

Edge cases

The file doesn’t shrink: the table’s pages become free space inside the file. Here, dropping a 2 MB table left the file at the same size until VACUUM:

$ ls -l shop.db | awk '{print $5, $NF}'
2076672 shop.db
$ sqlite3 shop.db 'DROP TABLE big;'
$ ls -l shop.db | awk '{print $5, $NF}'
2076672 shop.db
$ sqlite3 shop.db 'VACUUM;'
$ ls -l shop.db | awk '{print $5, $NF}'
24576 shop.db

See SQLite VACUUM.

There’s no DROP DATABASE: an SQLite database is a file, so delete the file instead (with no connections open):

$ sqlite3 shop.db 'DROP DATABASE shop;'
Error: in prepare, near "DATABASE": syntax error
  DROP DATABASE shop;
       ^--- error here
$ rm shop.db && ls shop.db
ls: shop.db: No such file or directory

Conclusion

Use DROP TABLE IF EXISTS in scripts you re-run. Check for views and foreign keys that depend on the table first, and drop child tables before parents. Run VACUUM afterwards if you want the disk space back.