ALTER TABLE in SQL

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 MODIFY is 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.

Related SQL Notes

• CREATE TABLE
• DROP TABLE
• Constraints