ALTER TABLE & DROP TABLE

Evolve your schema — add columns, rename tables, clean up

Beginner 10 minDDLALTERDROP

Changing Your Schema

Tables are not set in stone — real systems evolve. ALTER TABLE changes an existing table's structure; DROP TABLE removes it entirely. Here's the practical picture:

ADD COLUMN — The bread and butter of schema evolution. ALTER TABLE t ADD COLUMN priority TEXT DEFAULT 'normal' adds a column to an existing table; existing rows immediately get the DEFAULT value. This is safe, fast, and backward-compatible (old queries still work).

RENAME TABLE and RENAME COLUMN — ALTER TABLE t RENAME TO new_name works in all major databases. ALTER TABLE t RENAME COLUMN old TO new is supported in SQLite 3.25+, Postgres, and MySQL 8+. Rename operations are fast — they're metadata-only, no data movement.

DROP COLUMN varies — SQLite supports it since 3.35 (2021). Postgres and MySQL support it directly. The operation can be slow on large tables because it may rewrite the table. In older SQLite you must recreate the table without the column.

Modifying column types — ALTER TABLE t ALTER COLUMN c TYPE new_type in Postgres. ALTER TABLE t MODIFY c new_type in MySQL. SQLite does not support type changes directly — recreate the table if needed. Type changes are one of the riskier schema operations because existing data may not convert cleanly.

DROP TABLE — DROP TABLE t deletes the table and all its rows, indexes, and triggers. Irreversible. There's no "undo" — only backups save you. Use DROP TABLE IF EXISTS t to avoid an error if the table doesn't exist. Consider renaming to t_deleted_YYYYMMDD first as a safety net before dropping.

Foreign keys complicate drops — If another table has a FOREIGN KEY referencing this one, the DROP may fail (or cascade, depending on settings). DROP TABLE t CASCADE (Postgres) also drops dependent objects. SQLite with PRAGMA foreign_keys = ON prevents the drop unless you handle FKs first.

Knowledge Canvas

How ALTER / DROP Work

Evolve and remove tables

ALTER TABLE ADD COLUMN — safest change, backward compatible
ALTER TABLE RENAME TO / RENAME COLUMN — metadata only
ALTER TABLE DROP COLUMN — SQLite 3.35+, Postgres, MySQL
DROP TABLE — irreversible; the data is gone

ADD COLUMN Variants

NULL vs DEFAULT behavior

ADD COLUMN c TEXT

Existing rows get NULL

ADD COLUMN c TEXT DEFAULT 'x'

Existing rows get 'x'

ADD COLUMN c TEXT NOT NULL

Fails if rows exist and no DEFAULT

Schema Change Traps

Things that bite

DROP TABLE is irreversible — rename first, drop later
Adding NOT NULL without DEFAULT fails on non-empty tables
SQLite: ALTER COLUMN TYPE is not supported — recreate instead
Postgres: ALTER can rewrite the whole table (downtime)

Safer Alternatives

Production-safe migration habits

Backup before DROP
Use IF EXISTS / IF NOT EXISTS for re-runnable scripts
Rename to _deleted_YYYYMMDD before dropping
Test ALTER on a copy of production first

Syntax

Syntax Template

1-- Add a column (safe, fast, backward-compatible)

2ALTER TABLE t ADD COLUMN new_col TEXT DEFAULT 'x';

4-- Rename a table

5ALTER TABLE old_name RENAME TO new_name;

7-- Rename a column (SQLite 3.25+, Postgres, MySQL 8+)

8ALTER TABLE t RENAME COLUMN old_col TO new_col;

10-- Drop a column (SQLite 3.35+, Postgres, MySQL)

11ALTER TABLE t DROP COLUMN unused_col;

13-- Drop a table — irreversible!

14DROP TABLE IF EXISTS t;

16-- List tables (SQLite)

17SELECT name FROM sqlite_master WHERE type = 'table';

ADD COLUMN ... DEFAULTAdd a column with a default value for existing rows

RENAME TO / RENAME COLUMNRename table or column — metadata-only operation

DROP COLUMNRemove a column — may rewrite the table on older versions

DROP TABLE IF EXISTSRemove the table; IF EXISTS prevents errors if already gone

Worked Example

Create a tasks table, seed two rows, then add a priority column with a default, and finally rename the table to work_items.

SQL

1DROP TABLE IF EXISTS tasks;

2CREATE TABLE tasks (

3 id INTEGER PRIMARY KEY,

4 title TEXT,

5 done INTEGER DEFAULT 0

6);

8INSERT INTO tasks (id, title) VALUES

9 (1, 'Write query'),

10 (2, 'Review PR');

12-- Add a new column with a default — existing rows get 'normal'

13ALTER TABLE tasks ADD COLUMN priority TEXT DEFAULT 'normal';

15SELECT * FROM tasks ORDER BY id;

17-- Rename the table

18ALTER TABLE tasks RENAME TO work_items;

20-- Verify the new column and the new name

21PRAGMA table_info('work_items');

After CREATE + INSERT we have two rows. ALTER TABLE tasks ADD COLUMN priority TEXT DEFAULT 'normal' adds the new column and immediately fills it with "normal" for both existing rows — no separate UPDATE needed. Then RENAME TO work_items renames the table without touching any data. PRAGMA table_info confirms the final schema has four columns.

Output

2 rows

id	title	done	priority
1	Write query	0	normal
2	Review PR	0	normal

Common Mistakes

❌

DROP TABLE without a backup

SQL

1DROP TABLE orders;

Once dropped, the rows are gone. Not "moved to archive," not "soft-deleted" — gone. Recovering requires a backup (if you have one) or point-in-time recovery (if your database supports it).

Before dropping production data, rename first as insurance: ALTER TABLE orders RENAME TO orders_deleted_20240420;. Keep the renamed table for a week, then drop.

❌

ADD COLUMN NOT NULL without DEFAULT on a non-empty table

SQL

1ALTER TABLE customers ADD COLUMN phone TEXT NOT NULL;

Existing rows have no value for the new column — but NOT NULL refuses NULL values. The ALTER fails with a constraint error on most databases (Postgres, MySQL). SQLite allows it but treats existing values as if the column didn't exist until set.

Add the column with a DEFAULT first: ADD COLUMN phone TEXT NOT NULL DEFAULT 'unknown'. Or add it nullable, backfill values, then change to NOT NULL.

Key Concepts

1ALTER TABLE ADD COLUMN is the safest schema change — backward-compatible

2Renaming columns is supported in SQLite 3.25+, Postgres, MySQL — syntax varies

3DROP TABLE is irreversible — the data is gone, not moved to a recycle bin

4IF EXISTS / IF NOT EXISTS makes DDL statements safe to re-run

Pro Tip

Schemas are never done. Adding columns is the most common kind of change; doing it without locking the table for hours (on big systems) requires understanding what each operation costs.

When to Use

Adding new fields as features ship, renaming tables during refactors, dropping staging tables after successful migrations, deprecating old columns.

UPDATE & DELETE

DISTINCT