SQLite DROP COLUMN: How to Delete a Column From a Table (Tested)

Ninad Pathak

October 7, 2026

Dropping a column in SQLite has taken one line since version 3.35.0 in 2021: ALTER TABLE … DROP COLUMN. Older answers that say you have to copy the whole table are out of date. In this article I’ll walk you through dropping a column in one line, plus the table rebuild for the cases where SQLite refuses.

TLDR

Delete a column with ALTER TABLE table_name DROP COLUMN column_name;. It needs SQLite 3.35.0 or later.
It fails on a primary key or UNIQUE column. It also fails while another part of the schema uses the column, such as an index.

Drop the index first, then the column.
For a primary key or UNIQUE column, rebuild the table without that column and copy the rows across.

What does DROP COLUMN do in SQLite?

It removes a column and its data from a table. SQLite added it in version 3.35.0, released on 2021-03-12 (release notes). The docs say the command “removes the named column from the table, and rewrites its content to purge the data associated with that column” (SQLite ALTER TABLE).

Check your version with SELECT sqlite_version();. Anything from 3.35.0 on has DROP COLUMN.

SQLite DROP COLUMN syntax

ALTER TABLE table_name DROP COLUMN column_name;

The word COLUMN is optional (DROP pageviews works too), but writing it makes the statement clearer. You drop one column per statement.

How to delete a column in SQLite

To show you how it works, I will use a small analytics table with a pageviews column we no longer need.

Step 1: Create the table

sqlite> CREATE TABLE analytics (id INTEGER PRIMARY KEY, user_id INTEGER, event_type TEXT, ts TEXT, pageviews INTEGER);
sqlite> INSERT INTO analytics VALUES (1, 100, 'click', '2026-10-01 10:00', 10), (2, 101, 'purchase', '2026-10-01 11:30', 5), (3, 100, 'view', '2026-10-01 12:15', 20);

Step 2: Drop the column

sqlite> ALTER TABLE analytics DROP COLUMN pageviews;
sqlite> SELECT * FROM analytics;
id  user_id  event_type  ts
--  -------  ----------  ----------------
1   100      click       2026-10-01 10:00
2   101      purchase    2026-10-01 11:30
3   100      view        2026-10-01 12:15

Step 3: Check the table definition

SQLite rewrote the stored CREATE TABLE statement without the column:

sqlite> SELECT sql FROM sqlite_schema WHERE name = 'analytics';
sql
------------------------------------------------------------------------------------------
CREATE TABLE analytics (id INTEGER PRIMARY KEY, user_id INTEGER, event_type TEXT, ts TEXT)

Examples

The examples continue with the same analytics table.

Drop a column that has an index

An index on the column blocks the drop:

sqlite> CREATE INDEX idx_analytics_user ON analytics(user_id);
sqlite> ALTER TABLE analytics DROP COLUMN user_id;
Error: stepping, error in index idx_analytics_user after drop column: no such column: user_id

Drop the index first, then the column:

sqlite> DROP INDEX idx_analytics_user;
sqlite> ALTER TABLE analytics DROP COLUMN user_id;
sqlite> SELECT * FROM analytics;
id  event_type  ts
--  ----------  ----------------
1   click       2026-10-01 10:00
2   purchase    2026-10-01 11:30
3   view        2026-10-01 12:15

Delete a PRIMARY KEY or UNIQUE column by rebuilding the table

DROP COLUMN refuses both kinds of column:

sqlite> ALTER TABLE analytics DROP COLUMN id;
Error: in prepare, cannot drop PRIMARY KEY column: "id"

sqlite> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE);
sqlite> INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com'), ('Linus', 'linus@example.com');

sqlite> ALTER TABLE users DROP COLUMN email;
Error: in prepare, cannot drop UNIQUE column: "email"

For these, rebuild the table. SQLite’s docs describe a 12-step procedure for any schema change (Making other kinds of table schema changes). For a simple table, the core of it is this, inside one transaction:

-- Remove the UNIQUE column "email" by rebuilding the table.
PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE users_new (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO users_new (id, name) SELECT id, name FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
COMMIT;
PRAGMA foreign_keys = ON;

$ sqlite3 t.db < rebuild.sql
sqlite> SELECT * FROM users;
id  name
--  -----
1   Ada
2   Linus
sqlite> SELECT sql FROM sqlite_schema WHERE name = 'users';
sql
---------------------------
CREATE TABLE "users" (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
)

Re-create the table’s indexes and triggers after the rename. Then check foreign keys with PRAGMA foreign_key_check. This rebuild also works on SQLite versions older than 3.35.0.

Edge cases

Each of these is a drop that fails, or one that succeeds and leaves something broken.

Dropping a column twice

sqlite> ALTER TABLE analytics DROP COLUMN pageviews;
Error: in prepare, no such column: "pageviews"
  ALTER TABLE analytics DROP COLUMN pageviews;
                      error here ---^

A view that uses the column

SQLite’s docs list “the column appears in a trigger or view” as a reason the drop fails. In the macOS sqlite3 shell I used, though, the drop went through and broke the view:

sqlite> CREATE VIEW recent AS SELECT id, event_type FROM analytics;
sqlite> ALTER TABLE analytics DROP COLUMN event_type;
sqlite> SELECT * FROM recent;
Error: in prepare, no such column: event_type

That shell has legacy_alter_table switched on, while the documented default is off (PRAGMA legacy_alter_table):

sqlite> PRAGMA legacy_alter_table;
legacy_alter_table
------------------
1

With the default setting, in SQLite 3.53.4, the same drop fails and the view is safe:

import sqlite3

con = sqlite3.connect(":memory:")
print("SQLite", sqlite3.sqlite_version, "| legacy_alter_table =", con.execute("PRAGMA legacy_alter_table").fetchone()[0])
con.executescript("""
    CREATE TABLE analytics (id INTEGER PRIMARY KEY, event_type TEXT, ts TEXT);
    CREATE VIEW recent AS SELECT id, event_type FROM analytics;
""")
try:
    con.execute("ALTER TABLE analytics DROP COLUMN event_type")
except sqlite3.OperationalError as e:
    print("OperationalError:", e)

$ python3.13 view_check.py
SQLite 3.53.4 | legacy_alter_table = 0
OperationalError: error in view recent after drop column: no such column: event_type

Before you drop a column, fix any view or trigger that uses it.

Conclusion

On SQLite 3.35.0 or later, ALTER TABLE ... DROP COLUMN is the way to delete a column. Drop its indexes first. Rebuild the table when the column is a primary key or UNIQUE, or when you’re stuck on an older version. To rename a column instead, use ALTER TABLE ... RENAME COLUMN, and to add one, see SQLite ALTER TABLE ADD COLUMN.