ALTER TABLE (Add, Drop & Modify Columns)
Requirements change. A table you created last week needs a new column today, or a column that's no longer used. ALTER TABLE lets you reshape an existing table — adding, removing, renaming, and re-typing columns — without rebuilding it from scratch.
Learn ALTER TABLE (Add, Drop & Modify Columns) in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and…
Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.
By the end you'll add and drop columns safely, rename columns and tables, change a column's type, and even add constraints after the fact.
What You'll Learn
Our Starting Table: customers
We'll evolve this small table throughout the lesson.
1. ADD COLUMN
ALTER TABLE … ADD COLUMN appends a column. Existing rows get NULL in it unless you supply a DEFAULT , which backfills them instead.
ALTER TABLE is renovating a house you already live in — adding a room, knocking out a wall — instead of demolishing and rebuilding. The structure changes while the contents stay put.
2. DROP COLUMN
DROP COLUMN deletes a column and every value in it — permanently. There's no undo, so treat it with respect and back up first.
3. RENAME COLUMN (and Table)
Renaming keeps the data and just changes the label. RENAME COLUMN old TO new renames a column; RENAME TO renames the entire table.
4. Changing a Column's Type
This is the most engine-specific operation. Postgres/SQL Server use ALTER COLUMN ; MySQL uses MODIFY COLUMN . Widening a type is safe; narrowing it can fail if existing data won't fit.
Your Turn: add a timestamp
Fill in the action and type to add a last_login column to accounts .
5. Adding & Dropping Constraints
ALTER TABLE also manages constraints after a table exists — adding a UNIQUE rule, a FOREIGN KEY , or removing a named constraint. (We cover constraints in depth in the next lesson.)
6. Multiple Changes at Once
Postgres and MySQL let you comma-separate several actions in a single ALTER TABLE — one statement, many changes. (SQLite is more limited and wants one change per statement.)
Reorder Challenge
You need to safely add a NOT NULL "status" column to a populated table. Order these steps:
Quick Recall
1) You ADD COLUMN phone VARCHAR(20) with no default. What's in existing rows?
NULL . Without a DEFAULT, existing rows get NULL in the new column.
No. It permanently deletes the column and all its data. Restore from a backup is the only recovery.
3) Which keyword changes a column's type in MySQL?
MODIFY COLUMN . Postgres and SQL Server use ALTER COLUMN instead.
Common Errors (and the fix)
- NOT NULL on a populated table without DEFAULT: fails because existing rows have no value. Add nullable, backfill, then enforce.
- Wrong type-change keyword: MODIFY (MySQL) vs ALTER COLUMN (Postgres/SQL Server). Match your engine.
- Dropping a column other things depend on: views, constraints, or app code may break. Check dependencies first.
- Narrowing a type that won't fit: VARCHAR(30) → VARCHAR(5) errors if longer values exist.
- SQLite limitations: older SQLite can't drop/modify columns easily — recreate the table if needed.
📘 Quick Reference
Syntax
Purpose
Frequently Asked Questions
Mini-Challenge: Evolve the Employees Table
Write three ALTER TABLE statements to add two columns and rename one. Run them to confirm they're valid.
🎉 Lesson Complete
- ✅ ADD COLUMN appends a column (NULL or a DEFAULT for existing rows)
- ✅ DROP COLUMN is permanent — back up first
- ✅ RENAME COLUMN / RENAME TO change names, not data
- ✅ Type changes are engine-specific ( ALTER COLUMN vs MODIFY )
- ✅ You can add and drop constraints after a table exists
- ✅ Next: constraints in depth — PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK
Practice quiz
What does ALTER TABLE ... ADD COLUMN do?
- Deletes a column
- Renames the table
- Appends a new column to an existing table
- Adds a new row
Answer: Appends a new column to an existing table. ADD COLUMN appends a column to an existing table without rebuilding it.
When you ADD COLUMN with no DEFAULT, what do existing rows hold in it?
- NULL
- 0
- An empty string
- The column name
Answer: NULL. Without a DEFAULT, existing rows get NULL; a DEFAULT clause backfills them instead.
What is true of DROP COLUMN?
- It can be undone with ROLLBACK COLUMN
- It only hides the column
- It renames the column
- It permanently removes the column and all its data
Answer: It permanently removes the column and all its data. DROP COLUMN permanently deletes the column and every value in it; there is no built-in undo.
What does RENAME COLUMN full_name TO name do to the data?
- Deletes it
- Leaves the data untouched — only the name changes
- Resets it to NULL
- Copies it to a new table
Answer: Leaves the data untouched — only the name changes. Renaming changes only the label; the column's data is kept intact.
Which keyword changes a column's type in MySQL?
- MODIFY COLUMN
- ALTER COLUMN ... TYPE
- CHANGE TYPE
- SET TYPE
Answer: MODIFY COLUMN. MySQL uses MODIFY COLUMN, while PostgreSQL and SQL Server use ALTER COLUMN.
Why is widening VARCHAR(20) to VARCHAR(30) safer than narrowing?
- It is faster
- Narrowing is not allowed at all
- Existing values always fit in the wider type, but narrowing can fail if data is too long
- Widening copies the table
Answer: Existing values always fit in the wider type, but narrowing can fail if data is too long. Widening always fits existing data; narrowing can fail when current values are too long.
How do you safely add a NOT NULL column to a populated table?
- Add it NOT NULL directly
- Add it nullable, backfill with UPDATE, then add NOT NULL
- Drop the table first
- It is impossible
Answer: Add it nullable, backfill with UPDATE, then add NOT NULL. Add the column nullable, backfill every row, then enforce NOT NULL — or supply a DEFAULT.
What can ALTER TABLE ... ADD CONSTRAINT do?
- Only rename columns
- Delete all data
- Change the database name
- Add rules like UNIQUE or FOREIGN KEY to an existing table
Answer: Add rules like UNIQUE or FOREIGN KEY to an existing table. ALTER TABLE can add UNIQUE, FOREIGN KEY, and other constraints after a table exists.
Which engines let you comma-separate several changes in one ALTER TABLE?
- SQLite only
- PostgreSQL and MySQL
- No engine allows it
- Only SQL Server
Answer: PostgreSQL and MySQL. PostgreSQL and MySQL allow stacked comma-separated actions; SQLite does one change per statement.
In the reorder example, why must you backfill before SET NOT NULL?
- NOT NULL is faster after data exists
- Backfilling deletes old rows
- Existing rows are still NULL, so enforcing NOT NULL first would fail
- SET NOT NULL requires a default
Answer: Existing rows are still NULL, so enforcing NOT NULL first would fail. Add nullable, backfill values, then SET NOT NULL — otherwise the NULL rows violate the constraint.