ALTER TABLE Statement
SQL · Working with Tables
Theory
ALTER TABLE changes the structure of a table that already exists, while keeping its data. With it you can add a column, drop a column, rename a column or the table, change a column's data type or default, and add or drop constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE or CHECK. For example, ALTER TABLE employees ADD COLUMN hire_date DATE adds an empty column to every existing row.
Exact syntax varies: PostgreSQL uses ALTER COLUMN ... TYPE, MySQL uses MODIFY COLUMN, SQL Server uses ALTER COLUMN, and SQLite supports only a few actions (add, rename, drop column). Some changes are instant metadata updates, while others, like changing a type, rewrite every row and can lock a large table for a long time. Adding a constraint fails if existing rows already break it.
Why we use it
Requirements change after a table goes live: a new field is needed, a name was wrong, a rule must be enforced. ALTER TABLE evolves the table in place without dropping and reloading the data.
When to use it
Use ALTER TABLE for schema changes on existing tables, ideally through versioned migration scripts. For big type changes on huge tables, consider creating a new table and copying data, or run the change during a quiet window.
How it works
- ALTER TABLE employees ...
- Choose the action: ADD, DROP, RENAME, ALTER type, ADD CONSTRAINT
- Does existing data satisfy the change? Yes: Apply the change. No: Error: fix the data first.
- Rewrites every row? Yes: Expect locks and time on big tables. No: Metadata-only change, fast.
- Table has the new structure
Syntax
ALTER TABLE table_name
ADD | DROP | MODIFY column ...;Example
ALTER TABLE employees ADD COLUMN hire_date DATE;Key points
- Can add, drop, rename or modify columns and constraints
- Some changes lock the table on large datasets
- Syntax for changing types differs by engine (ALTER vs MODIFY)
- Adding a constraint fails if current rows violate it
- SQLite supports only a small set of ALTER actions