ALTER TABLE (Add, Drop & Modify Columns)

Reviewed & published by Brayan K

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.

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

• Add a new column with ADD COLUMN
• Remove a column with DROP COLUMN (carefully!)
• Rename columns and whole tables
• Change a column's data type per engine
• Add and drop constraints after creation
• Stack multiple changes in one statement

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.

-- The tables this lesson uses. Run this block first;
-- every later block changes them.
CREATE TABLE customers (
    id        INTEGER PRIMARY KEY,
    full_name TEXT,
    email     TEXT
);
INSERT INTO customers VALUES
    (1, 'Ava', 'ava@x.com'),
    (2, 'Ben', 'ben@x.com');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (100, 1, 40), (101, 2, 75);

CREATE TABLE accounts (id INTEGER PRIMARY KEY, username TEXT);
INSERT INTO accounts VALUES (1, 'ava'), (2, 'ben');

-- ADD COLUMN appends a new column to an existing table.
ALTER TABLE customers
ADD COLUMN phone VARCHAR(20);

-- Give it a DEFAULT and the existing rows are backfilled with that
-- instead of NULL — compare the two new columns below.
ALTER TABLE customers
ADD COLUMN active BOOLEAN DEFAULT TRUE;

SELECT * FROM customers ORDER BY id;

-- ✅ Expected result:
-- id | full_name | email | phone | active
-- 1 | Ava | ava@x.com | NULL | 1
-- 2 | Ben | ben@x.com | NULL | 1

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.

-- Uses the customers table from the first example.
-- DROP COLUMN permanently removes a column AND its data.
ALTER TABLE customers
DROP COLUMN phone;

-- The phone column and every phone value are gone for good.
-- ⚠️ There is no undo — back up first if the data matters.
-- (Postgres also lets you guard it: DROP COLUMN IF EXISTS phone.)

SELECT * FROM customers ORDER BY id;

-- ✅ Expected result:
-- id | full_name | email | active
-- 1 | Ava | ava@x.com | 1
-- 2 | Ben | ben@x.com | 1

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.

-- Uses the customers table from the first example.
-- RENAME COLUMN changes a column's name, keeping its data.
ALTER TABLE customers
RENAME COLUMN full_name TO name;

-- The values are untouched — only the heading changes.
-- You can rename the whole table the same way:
--   ALTER TABLE customers RENAME TO clients;
-- It is left commented out here so the later examples can still
-- say "customers".

SELECT * FROM customers ORDER BY id;

-- ✅ Expected result:
-- id | name | email | active
-- 1 | Ava | ava@x.com | 1
-- 2 | Ben | ben@x.com | 1

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.

-- engine: postgres — from here on the lesson needs a PostgreSQL server.
-- SQLite can add, drop and rename columns but cannot change a column's
-- TYPE or add a constraint after the fact, so pressing Run on this site
-- (which is SQLite in your browser) will refuse the next three blocks.
-- That is the engine, not the snippet.
--
-- Uses the customers table from the first example.
-- Changing a column's TYPE — the syntax differs by engine:
--   Postgres   : ALTER TABLE t ALTER COLUMN c TYPE VARCHAR(30);
--   MySQL      : ALTER TABLE t MODIFY COLUMN c VARCHAR(30);
--   SQL Server : ALTER TABLE t ALTER COLUMN c VARCHAR(30);

-- Put the phone column back (block 2 dropped it) and then widen it:
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
ALTER TABLE customers ALTER COLUMN phone TYPE VARCHAR(30);

-- ⚠️ Widening (VARCHAR(20) -> VARCHAR(30)) is safe.
-- Narrowing or changing type can fail if existing data won't fit.
-- Ask the catalogue what the column is now:
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'customers' AND column_name = 'phone';

-- ✅ Expected result:
-- column_name | data_type | character_maximum_length
-- phone | character varying | 30

Your Turn: add a timestamp

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

-- 🎯 YOUR TURN — fill in the blanks
-- Uses the accounts table from the first example.
-- Goal: add a "last_login" TIMESTAMP column to the accounts table.

ALTER TABLE accounts
___ COLUMN last_login ___;   -- 👉 replace ___ with ADD, then ___ with TIMESTAMP

-- Existing rows show NULL in it until something sets them.
SELECT * FROM accounts ORDER BY id;

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

-- Uses the customers and orders tables from the first example.
-- ALTER TABLE can also add and drop CONSTRAINTS after the fact.

-- Make email unique (Postgres/MySQL/SQL Server):
ALTER TABLE customers
ADD CONSTRAINT uq_email UNIQUE (email);

-- Add a foreign key linking orders to customers:
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Naming them is the point: a named constraint can be dropped again,
--   ALTER TABLE customers DROP CONSTRAINT uq_email;
-- whereas an unnamed one gets a generated name you have to look up.
-- Both of ours are listed here:
SELECT conname, contype
FROM pg_constraint
WHERE conname IN ('uq_email', 'fk_customer')
ORDER BY conname;

-- ✅ Expected result:
-- conname | contype
-- fk_customer | f
-- uq_email | u

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

-- Some engines let you stack changes in one statement (comma-separated).
-- This block makes its own small table so the three changes are visible.
CREATE TABLE products (
    id        INTEGER PRIMARY KEY,
    name      TEXT,
    weight_kg NUMERIC
);
INSERT INTO products VALUES (1, 'Kettle', 1.4);

ALTER TABLE products
ADD COLUMN sku       VARCHAR(12),
ADD COLUMN on_sale   BOOLEAN DEFAULT FALSE,
DROP COLUMN weight_kg;

-- One trip to the table, three structural changes.
-- (SQLite is limited — it does one change per ALTER statement.)
SELECT * FROM products ORDER BY id;

-- ✅ Expected result:
-- id | name | sku | on_sale
-- 1 | Kettle | NULL | f

Reorder Challenge

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

A.   ALTER TABLE orders ALTER COLUMN status SET NOT NULL;
B.   ALTER TABLE orders ADD COLUMN status VARCHAR(20);
C.   UPDATE orders SET status = 'pending';

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.

2) Can you undo a DROP 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.

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.

-- 🎯 MINI-CHALLENGE
-- The "employees" table needs three changes. Write them:
--   1. Add a column "department" (text up to 50 chars)
--   2. Add a column "hired_on" (a DATE) defaulting to today (CURRENT_DATE)
--   3. Rename the column "fname" to "first_name"
--
-- ✅ Expected: three valid ALTER TABLE statements that run cleanly.

-- your ALTER TABLE statements here

🎉 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

Continue this course

• Previous: CREATE TABLE & Data Types
• Next: Constraints (PK, FK, UNIQUE, CHECK) — Enforce data integrity with primary/foreign keys and constraints
• Quick reference: SQL cheat sheet › Tables & Schema

Frequently asked questions

Is ALTER TABLE reversible?

Not automatically. ADD COLUMN can be undone with DROP COLUMN, but DROP COLUMN itself permanently deletes the column and all its data — there's no built-in undo. Always back up (or wrap changes in a transaction where supported) before destructive ALTERs, especially on a production database.

Why does the type-change syntax differ between databases?

ALTER COLUMN is one of the least standardized statements. PostgreSQL and SQL Server use ALTER COLUMN ... TYPE / ALTER COLUMN, MySQL uses MODIFY COLUMN, and SQLite historically supported only adding and renaming columns. Check your engine's docs before changing a column's type.

What happens to existing rows when I ADD a column?

They get NULL in the new column by default. If you add the column with a DEFAULT clause, the engine backfills existing rows with that default value instead. If the column is NOT NULL, you must supply a DEFAULT (or the statement fails on a non-empty table) so existing rows have a value.

Can I add a NOT NULL column to a table that already has data?

Only if you also give it a DEFAULT, otherwise the existing rows would have no value and violate the constraint. The safe pattern is: add the column as nullable, backfill it with an UPDATE, then add the NOT NULL constraint with a second ALTER.