ALTER TABLE

Schemas evolve. ALTER TABLE lets you change an existing table's structure — add or remove columns, change a column's type, rename things, and add or drop constraints — without dropping and recreating the table (and losing its data) from scratch.

Adding a column

ADD COLUMN

SQL

ALTER TABLE users
ADD COLUMN phone_number VARCHAR(20);

-- With a default applied to existing rows
ALTER TABLE users
ADD COLUMN is_verified BOOLEAN NOT NULL DEFAULT FALSE;

Dropping a column

DROP COLUMN

SQL

ALTER TABLE users
DROP COLUMN phone_number;

Warning

`DROP COLUMN` deletes that column's data for every row, immediately and irreversibly (short of restoring from a backup). There is no confirmation prompt and no `WHERE` clause to limit it — the entire column is gone the moment the statement runs. Double-check the table and column name, and confirm you don't need that data anywhere, before running it against a real database.

Changing a column's type

The syntax for changing a column's type differs by dialect. PostgreSQL uses ALTER COLUMN ... TYPE; MySQL uses MODIFY COLUMN.

Changing a column type (PostgreSQL)

SQL

ALTER TABLE users
ALTER COLUMN phone_number TYPE VARCHAR(30);

Changing a column type (MySQL)

SQL

ALTER TABLE users
MODIFY COLUMN phone_number VARCHAR(30);

Renaming a column or table

Renaming

SQL

-- Rename a column
ALTER TABLE users
RENAME COLUMN full_name TO display_name;

-- Rename the table itself
ALTER TABLE users
RENAME TO app_users;

Adding and dropping constraints

Constraints via ALTER TABLE

SQL

-- Add a NOT NULL constraint retroactively
ALTER TABLE users
ALTER COLUMN display_name SET NOT NULL;

-- Add a named CHECK constraint
ALTER TABLE users
ADD CONSTRAINT age_non_negative CHECK (age >= 0);

-- Drop a named constraint
ALTER TABLE users
DROP CONSTRAINT age_non_negative;

ALTER TABLE on large production tables

Warning

On a small table, `ALTER TABLE` runs instantly and nobody notices. On a large, actively-used production table, many `ALTER TABLE` operations — especially adding a column with a non-null default, changing a column's type, or adding certain constraints — can require rewriting the entire table or take an exclusive lock on it for the whole operation. That can mean minutes (or longer) of blocked reads and writes on a table your application depends on every second. Before running a structural change on a large live table, check your specific database version's documentation for which operations are "online"/non-blocking, and consider running the change during low-traffic hours or with a specialized zero-downtime-migration tool.

Multiple changes in one statement

Most dialects let you combine several `ALTER TABLE` actions into one statement (`ALTER TABLE t ADD COLUMN a INT, DROP COLUMN b;` in PostgreSQL and MySQL), which can be more efficient than separate statements. SQL Server generally requires them as a comma-separated list within a single `ALTER TABLE` too, but exact support for combining certain operations varies — check your dialect's docs for anything beyond simple add/drop combinations.

Adding a nullable column (or one with a constant default in modern PostgreSQL/MySQL) is usually fast even on large tables.
Adding a NOT NULL column without a default, or one that requires computing a value per row, is typically the slowest and most locking-prone change.
Always test structural changes against a copy of production-scale data before running them on the real thing.
Keep a rollback plan (a script that reverses the change) ready for any non-trivial ALTER TABLE in a live system.