🏗️ SchemaintermediateCommonly used

How to alter a table in SQL

Add, remove or modify columns and constraints on an existing table.

Add a columnAll databases

ALTER TABLE users
ADD COLUMN phone TEXT;

Adds a new column to an existing table. New column defaults to NULL for existing rows.

Add a column with a defaultAll databases

ALTER TABLE users
ADD COLUMN score INTEGER DEFAULT 0 NOT NULL;

Adds a column with a default value. Existing rows get the default.

Rename a columnAll databases

ALTER TABLE users
RENAME COLUMN username TO name;

Renames a column without affecting the data.

Drop a columnAll databases

ALTER TABLE users
DROP COLUMN phone;

Permanently removes a column and all its data.

Cannot be undone. Backup data first.

Change column type (PostgreSQL)PostgreSQL

ALTER TABLE users
ALTER COLUMN age TYPE BIGINT;

Changes the data type of a column.

Can fail if existing data is incompatible with the new type.

Add a constraintAll databases

ALTER TABLE orders
ADD CONSTRAINT chk_total CHECK (total >= 0);

Adds a CHECK constraint that validates all future and existing data.

Rename a tableAll databases

ALTER TABLE users
RENAME TO customers;

Renames the entire table.

Common Mistakes

→ALTER TABLE on large tables can lock the table — use tools like pg_repack or online schema change in production
→Dropping columns is permanent — always backup first