ALTER TABLE changes the structure of an existing table — add, modify, rename, or drop columns — without touching the data already in unaffected columns.
Add a Column
ALTER TABLE employees
ADD COLUMN email VARCHAR(150);Modify a Column's Type — Dialect Differences
-- MySQL
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2);
-- PostgreSQL
ALTER TABLE employees ALTER COLUMN salary TYPE DECIMAL(12,2);
-- SQL Server
ALTER TABLE employees ALTER COLUMN salary DECIMAL(12,2);This is one of the few operations that genuinely differs across databases — MySQL's MODIFY keyword has no equivalent in standard SQL.
Rename a Column
-- MySQL 8+
ALTER TABLE employees RENAME COLUMN dept TO department;
-- PostgreSQL
ALTER TABLE employees RENAME COLUMN dept TO department;Drop a Column
ALTER TABLE employees
DROP COLUMN middle_name;Practical Use Case
Requirements change after launch — a new feature needs an extra column, or a column's type turns out too small (e.g. VARCHAR(50) for names that turn out to be longer). ALTER TABLE is how you evolve a live schema without recreating the table.
Common Mistakes
- Dropping a column that's referenced elsewhere (views, foreign keys, application code) without checking first
- Running a type change on a huge table during peak hours — on large tables this can lock the table and slow down production traffic
- Forgetting that
MODIFYis MySQL-only syntax and using it against PostgreSQL
Interview Relevance
A common scenario question: "How would you add a non-nullable column to a table that already has data, without breaking existing rows?" Answer: add it with a DEFAULT value, or add it nullable first, backfill the data, then apply a NOT NULL constraint afterward.
Practice Question
Write the statement to add a phone_number VARCHAR(15) column to a customers table, then rename it to contact_number.