Altering & Dropping Tables

Schemas rarely stay perfect forever — RecipeBox might need a new column, a renamed table, or a column that never should have existed. SQLite's ALTER TABLE support is intentionally limited compared to something like MySQL: it handles a handful of common changes directly, and for everything else there's a standard workaround. This lesson covers both.

What ALTER TABLE Supports Directly

SQLite's ALTER TABLE can do exactly four things: rename a table, rename a column, add a new column, and drop a column. That's it — there's no direct way to change a column's type, add or remove a constraint, or reorder columns.

RENAME TO and RENAME COLUMN

-- Rename the whole table
ALTER TABLE recipes RENAME TO saved_recipes;

-- Rename it back
ALTER TABLE saved_recipes RENAME TO recipes;

-- Rename a column: prep_minutes -> prep_time_minutes
ALTER TABLE recipes RENAME COLUMN prep_minutes TO prep_time_minutes;

ADD COLUMN

Adding a column is the most common schema change. Every existing row gets the new column filled in with NULL, or with a DEFAULT value if you specify one.

-- Every existing recipe gets cook_time_minutes = NULL
ALTER TABLE recipes ADD COLUMN cook_time_minutes INTEGER;

-- Every existing recipe gets is_public = 1 instead of NULL
ALTER TABLE recipes ADD COLUMN is_public INTEGER NOT NULL DEFAULT 1;

DROP COLUMN

DROP COLUMN removes a column and every value stored in it. This is a genuinely recent addition to SQLite — it only became available in SQLite 3.35.0, released in March 2021. Older SQLite builds (including some still bundled with older Android or PHP installs) don't support it at all, so it's worth checking your SQLite version before relying on it in production.

-- Requires SQLite 3.35.0 or later
ALTER TABLE recipes DROP COLUMN is_public;

-- Check your SQLite version
SELECT sqlite_version();

The Workaround: What ALTER TABLE Can't Do

SQLite's ALTER TABLE cannot change a column's data type, add or remove a CHECK/UNIQUE/NOT NULL constraint, or change a DEFAULT value. When you need any of those, the standard SQLite technique is: create a new table with the schema you actually want, copy the old data into it, drop the old table, then rename the new one into its place.

Say RecipeBox stored prep_time_minutes as TEXT by mistake, and it needs to become a properly constrained INTEGER:

-- 1. Create a new table with the corrected schema
CREATE TABLE recipes_new (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  prep_time_minutes INTEGER CHECK(prep_time_minutes > 0),
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- 2. Copy the data across, converting the type on the way in
INSERT INTO recipes_new (id, user_id, title, prep_time_minutes, created_at)
SELECT id, user_id, title, CAST(prep_time_minutes AS INTEGER), created_at
FROM recipes;

-- 3. Drop the old table
DROP TABLE recipes;

-- 4. Rename the new table into its place
ALTER TABLE recipes_new RENAME TO recipes;

DROP TABLE

DROP TABLE deletes a table and all of its data permanently. Adding IF EXISTS makes the statement safe to run even when you're not sure the table exists — without it, dropping a table that isn't there raises an error instead of quietly doing nothing.

-- Errors if recipes_new doesn't exist
DROP TABLE recipes_new;

-- Safe either way
DROP TABLE IF EXISTS recipes_new;