Altering & Dropping Tables

Requirements change: a table needs a new column, a column needs renaming, an old table must go. ALTER TABLE changes an existing table's structure, and DROP TABLE removes it. On a live database these are among the riskiest statements you can run, so this lesson also covers doing them safely.

Adding a column

ALTER TABLE customers ADD COLUMN phone TEXT;

SELECT id, name, phone FROM customers WHERE id <= 3;

Existing rows get NULL (or the column's default) in the new column.

ALTER TABLE products ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT TRUE;

SELECT name, is_active FROM products WHERE id <= 3;

A new NOT NULL column must have a default, otherwise existing rows would break the rule.

Renaming

ALTER TABLE customers RENAME COLUMN signup_date TO joined_on;
ALTER TABLE departments RENAME TO teams;

SELECT name, joined_on FROM customers WHERE id = 1;
SELECT * FROM teams WHERE id = 1;

Renaming updates the table's definition, but any application code, views or reports that use the old name will break — search before you rename. (SQL Server uses the sp_rename procedure instead.)

Dropping a column

ALTER TABLE customers ADD COLUMN notes TEXT;
ALTER TABLE customers DROP COLUMN notes;

SELECT name FROM pragma_table_info('customers');

The column and all its data are gone. SQLite (3.35+) refuses to drop columns that are part of a key, index or constraint.

Changing a column's type or constraints

ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(10, 2);
ALTER TABLE products ALTER COLUMN category SET NOT NULL;
ALTER TABLE products ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

SQLite's ALTER TABLE is limited to adding, renaming and dropping columns and renaming tables. For anything else, the documented approach is to create a new table with the right definition, copy the data across, drop the old table and rename the new one — inside a transaction.

Dropping tables

CREATE TABLE old_promotions (id INTEGER PRIMARY KEY, name TEXT);
DROP TABLE old_promotions;
DROP TABLE IF EXISTS old_promotions;

SELECT name FROM sqlite_schema WHERE type = 'table' ORDER BY name;

DROP TABLE deletes the table, its data, its indexes and its triggers permanently. IF EXISTS avoids an error when it's already gone. A table referenced by another table's foreign key can't be dropped while those references exist:

DROP TABLE customers;

Error: FOREIGN KEY constraint failed

Changing schemas safely: migrations

• Use migration files. Store every schema change as a numbered SQL file in version control and apply them with a tool (Flyway, Liquibase, Alembic, Rails or Django migrations, Prisma…). Every environment then has the same schema.
• Test on a copy of production data first.
• Mind locks. On big tables, some changes rewrite every row and block writes. Check your database's documentation for which changes are instant.
• Expand, then contract. To rename a column with zero downtime: add the new column, write to both, backfill, switch reads, and only later drop the old one.
• Back up before destructive changes.

Try it yourself

Add a discount_pct column to products, set it for toys, and show the discounted prices.