SQLite DROP TABLE and ALTER TABLE: Schema Changes Explained

Schemas Change. SQLite Lets You Change Them — Mostly.

Once a table exists, you'll eventually want to rename it, add a column, drop a column, or restructure the whole thing. SQLite supports the common cases directly with DROP TABLE and ALTER TABLE, and gives you a documented workaround for everything else.

The catch: SQLite's ALTER TABLE is much more limited than Postgres or MySQL. Knowing what it can and can't do — and the rebuild pattern for the can'ts — is most of the skill here.

DROP TABLE Removes a Table and Everything Attached

DROP TABLE deletes the table, its rows, its indexes, and any triggers defined on it. There's no undo:

CREATE TABLE scratch (
    id INTEGER PRIMARY KEY,
    note TEXT
);

INSERT INTO scratch (note) VALUES ('temporary'), ('also temporary');

DROP TABLE scratch;

SELECT name FROM sqlite_master WHERE type = 'table';

The table is gone. Querying it now would raise no such table: scratch.

If you're not sure whether the table exists — common in setup scripts — use IF EXISTS so the statement quietly does nothing when it's missing:

CREATE TABLE scratch (id INTEGER PRIMARY KEY);

DROP TABLE IF EXISTS scratch;
DROP TABLE IF EXISTS scratch;
SELECT 'both drops succeeded' AS result;

Without IF EXISTS, the second drop would error. With it, both run cleanly.

Foreign Keys Can Block a DROP

If foreign key enforcement is on (PRAGMA foreign_keys = ON;) and another table references the one you're dropping, the drop fails:

sqlite> PRAGMA foreign_keys = ON;
sqlite> DROP TABLE users;
Runtime error: FOREIGN KEY constraint failed

You have a few options: drop the referencing table first, delete the referencing rows, or define the foreign key with ON DELETE CASCADE when you create it. SQLite won't silently break referential integrity for you.

ALTER TABLE: The Four Things It Can Do

SQLite's ALTER TABLE supports exactly four operations:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT
);

-- 1. Rename the table
ALTER TABLE posts RENAME TO articles;

-- 2. Rename a column
ALTER TABLE articles RENAME COLUMN body TO content;

-- 3. Add a column
ALTER TABLE articles ADD COLUMN published_at TEXT;

-- 4. Drop a column (SQLite 3.35+)
ALTER TABLE articles DROP COLUMN published_at;

SELECT sql FROM sqlite_master WHERE name = 'articles';

Each of these runs as a single statement. The first two are basically free — they just update the schema. ADD COLUMN is also fast: SQLite doesn't rewrite the table, it just records the new column definition. DROP COLUMN is heavier — SQLite has to rewrite every row to physically remove the column's data.

ADD COLUMN With a Default

A new column on an existing table starts out NULL for every row, unless you give it a default:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL
);

INSERT INTO users (email) VALUES ('ada@example.com'), ('boris@example.com');

ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

SELECT * FROM users;

Both existing rows get 'active' filled in. The default has to be a constant — SQLite won't let you use CURRENT_TIMESTAMP or any other non-constant expression as the default in ADD COLUMN, because it needs a value it can apply to every existing row without evaluating per-row.

If you need NOT NULL without a default, you'll have to add the column nullable, backfill it with an UPDATE, and then rebuild the table to add the constraint. Which brings us to the limitations.

What ALTER TABLE Cannot Do

Things that work in Postgres or MySQL but not in SQLite:

• Change a column's type (ALTER COLUMN ... TYPE ...).
• Change a column's default in place.
• Add or remove NOT NULL, CHECK, UNIQUE, or PRIMARY KEY on an existing column.
• Add a foreign key to an existing column.
• Reorder columns.

Trying any of these gives you a syntax error. SQLite doesn't have an ALTER COLUMN clause at all. The official answer is the same for all of them: rebuild the table.

The Rebuild Pattern

When ALTER TABLE can't do what you need, you create a new table with the schema you want, copy the data over, drop the old one, and rename the new one into place. Wrap it in a transaction so it's all-or-nothing:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT,
    age TEXT
);

INSERT INTO users (email, age) VALUES ('ada@example.com', '36'), ('boris@example.com', '41');

BEGIN;

CREATE TABLE users_new (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER CHECK (age > 0)
);

INSERT INTO users_new (id, email, age)
SELECT id, email, CAST(age AS INTEGER) FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

COMMIT;

SELECT * FROM users;

Now users.age is an integer with a check constraint, and email is NOT NULL. The data came along for the ride.

A few things to keep in mind when you do this for real:

• Turn off foreign keys for the duration. If other tables reference yours, run PRAGMA foreign_keys = OFF; before the transaction and PRAGMA foreign_keys = ON; after. Otherwise the DROP TABLE will fail. The pragma can't be changed inside a transaction, so set it outside.
• Recreate indexes and triggers. Dropping the old table drops its indexes and triggers too. Add them back on the new table after the rename.
• Check views. Views that reference the table still point to the old name in their stored SQL. Rebuild any that depend on changed columns.

The rebuild pattern is verbose but reliable. It's what migration tools like Alembic and Rails do under the hood when they target SQLite.

Dropping Multiple Tables

There's no single statement for dropping multiple tables — you run DROP TABLE for each one. Inside a transaction if you want them grouped:

CREATE TABLE a (id INTEGER);
CREATE TABLE b (id INTEGER);
CREATE TABLE c (id INTEGER);

BEGIN;
DROP TABLE a;
DROP TABLE b;
DROP TABLE c;
COMMIT;

SELECT name FROM sqlite_master WHERE type = 'table';

Wrapping them in a transaction means either all three drops succeed or none do — useful when tearing down related tables that might fail on foreign keys partway through.

What You Take Away

• DROP TABLE removes a table and its indexes/triggers. Use IF EXISTS for idempotent scripts.
• ALTER TABLE only does four things: rename table, rename column, add column, drop column.
• For anything else — type changes, new constraints, foreign keys on existing columns — rebuild the table inside a transaction.
• Mind foreign keys, indexes, triggers, and views when rebuilding. They don't follow the data automatically.

Next: Putting Data In

You've spent a chapter on tables and the constraints that shape them. Time to fill them up — the next chapter starts with INSERT, including the multi-row form, default values, and how SQLite handles inserts that conflict with your constraints.