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.

We'll evolve this small table throughout the lesson.

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.

DROP COLUMN deletes a column and every value in it — permanently. There's no undo, so treat it with respect and back up first.

Renaming keeps the data and just changes the label. RENAME COLUMN old TO new renames a column; RENAME TO renames the entire table.

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.

Fill in the action and type to add a last_login column to accounts .

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.)

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.)

You need to safely add a NOT NULL "status" column to a populated table. Order these steps:

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.

Write three ALTER TABLE statements to add two columns and rename one. Run them to confirm they're valid.

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.