ALTER TABLE – Modifying Existing Tables

ADD COLUMN

Adding a column is the most common schema change. The new column is appended to the end of the row (or placed with AFTER in MySQL).

SQL

-- Add a single column (works in MySQL, PostgreSQL, SQLite)
ALTER TABLE users
  ADD COLUMN phone VARCHAR(20);

-- Add with constraints
ALTER TABLE users
  ADD COLUMN last_login TIMESTAMP NULL;

-- MySQL: control column position with AFTER
ALTER TABLE users
  ADD COLUMN middle_name VARCHAR(100) AFTER first_name;

-- MySQL: place column first
ALTER TABLE users
  ADD COLUMN uuid CHAR(36) FIRST;

-- Add multiple columns in one statement (MySQL)
ALTER TABLE products
  ADD COLUMN weight DECIMAL(8,3),
  ADD COLUMN dimensions VARCHAR(100);

💡

New columns default to NULL for existing rows

When you add a column to a table that already has data, all existing rows will have NULL in the new column (unless you specify a DEFAULT value). If you add a NOT NULL column without a default to a table with existing rows, the operation will fail. Always provide a DEFAULT value when adding a NOT NULL column to a populated table.

MODIFY / ALTER COLUMN

Changing a column's data type, constraints, or default value uses different keywords depending on the database.

SQL

-- MySQL: MODIFY COLUMN (restate the full column definition)
ALTER TABLE users
  MODIFY COLUMN phone VARCHAR(30) NOT NULL DEFAULT '';

-- MySQL: CHANGE COLUMN (rename AND modify in one step)
ALTER TABLE users
  CHANGE COLUMN phone mobile_phone VARCHAR(30);

-- PostgreSQL: ALTER COLUMN (each change is a separate clause)
ALTER TABLE users
  ALTER COLUMN phone TYPE VARCHAR(30);

ALTER TABLE users
  ALTER COLUMN phone SET NOT NULL;

ALTER TABLE users
  ALTER COLUMN phone SET DEFAULT '';

ALTER TABLE users
  ALTER COLUMN phone DROP DEFAULT;

-- Multiple PostgreSQL changes in one statement
ALTER TABLE users
  ALTER COLUMN phone TYPE VARCHAR(30),
  ALTER COLUMN phone SET NOT NULL;

RENAME COLUMN

Renaming a column was not part of the SQL standard for a long time, so syntax varies. Most modern databases now support a clean RENAME COLUMN syntax.

SQL

-- MySQL 8.0+ / PostgreSQL / SQLite 3.25+
ALTER TABLE users
  RENAME COLUMN phone TO mobile_phone;

-- MySQL older versions: use CHANGE COLUMN
ALTER TABLE users
  CHANGE COLUMN phone mobile_phone VARCHAR(20);

-- PostgreSQL (also supported)
ALTER TABLE users
  RENAME COLUMN phone TO mobile_phone;

DROP COLUMN

Dropping a column permanently removes it and all its data. This operation is irreversible — always take a backup first.

SQL

-- MySQL / PostgreSQL
ALTER TABLE users
  DROP COLUMN middle_name;

-- MySQL: multiple drops in one statement
ALTER TABLE users
  DROP COLUMN middle_name,
  DROP COLUMN phone;

-- SQLite limitation: SQLite did not support DROP COLUMN
-- until version 3.35.0 (2021). On older SQLite, you must
-- recreate the table without the column.
ALTER TABLE users
  DROP COLUMN middle_name;  -- works on SQLite 3.35+

⚠️

DROP COLUMN is destructive and cannot be rolled back outside a transaction

The column and all its data are gone immediately. In MySQL's InnoDB, this cannot be undone after commit. Always verify you have a recent backup before dropping a column in production. Consider a soft-delete approach: add an is_active = 0 flag instead of dropping, and remove the column in a later, low-risk migration.

RENAME TABLE

You can rename an entire table in a single statement.

SQL

-- MySQL
RENAME TABLE old_name TO new_name;
-- Or equivalently:
ALTER TABLE old_name RENAME TO new_name;

-- PostgreSQL
ALTER TABLE old_name RENAME TO new_name;

-- SQLite
ALTER TABLE old_name RENAME TO new_name;

ADD CONSTRAINT

You can add constraints to an existing table without recreating it. This is commonly used to add a foreign key after both tables have been created.

SQL

-- Add a FOREIGN KEY constraint
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user
  FOREIGN KEY (user_id) REFERENCES users(id)
  ON DELETE CASCADE;

-- Add a UNIQUE constraint
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Add a CHECK constraint (MySQL 8.0.16+, PostgreSQL, SQLite 3.25+)
ALTER TABLE products
  ADD CONSTRAINT chk_positive_price CHECK (price > 0);

-- Drop a named constraint
ALTER TABLE orders
  DROP CONSTRAINT fk_orders_user;   -- PostgreSQL
-- MySQL uses a different keyword:
ALTER TABLE orders
  DROP FOREIGN KEY fk_orders_user;

Production Migration Warning

⚠️

ALTER TABLE locks the table on large datasets

Some ALTER TABLE operations — particularly changing a column type or adding a NOT NULL column — require a full table rebuild. On a table with millions of rows, this can take minutes or hours and will lock the table for writes (or reads, depending on the operation) during that time, causing downtime. In production, use an online schema change tool: pt-online-schema-change (Percona Toolkit) or gh-ost (GitHub) for MySQL; PostgreSQL supports many operations as CONCURRENT alternatives (e.g., CREATE INDEX CONCURRENTLY). Always test schema changes on a staging environment first.

Summary

📋 Summary

ADD COLUMN appends a new column. New column is NULL for existing rows unless a DEFAULT is given.
MODIFY COLUMN (MySQL) / ALTER COLUMN (PostgreSQL) changes type, constraints, or default.
RENAME COLUMN renames a column (MySQL 8+, PostgreSQL, SQLite 3.35+). Use CHANGE COLUMN in older MySQL.
DROP COLUMN is permanent — back up first. Requires SQLite 3.35+.
RENAME TABLE: RENAME TABLE old TO new (MySQL) or ALTER TABLE old RENAME TO new (all).
ADD CONSTRAINT adds foreign keys, unique constraints, and checks to existing tables.
Use online schema change tools (pt-osc, gh-ost) for large production tables to avoid downtime.

FAQ

Why does MODIFY COLUMN require the full column definition in MySQL? +

MySQL's MODIFY COLUMN replaces the entire column definition, not just the part you want to change. If you only specify the new type without repeating the NOT NULL constraint, MySQL will silently remove that constraint. Always include all constraints in a MODIFY COLUMN statement — type, nullability, default, and any other attributes. PostgreSQL avoids this pitfall by using separate ALTER COLUMN ... TYPE, SET NOT NULL, and SET DEFAULT sub-clauses.

Can I ALTER TABLE inside a transaction? +

In PostgreSQL, DDL statements including ALTER TABLE are fully transactional — you can roll them back if something goes wrong. In MySQL with InnoDB, DDL statements cause an implicit commit before and after they execute, so they cannot be rolled back. This is a key reason to use migration tools and test schema changes on staging before production in MySQL environments.

How do I add a NOT NULL column to a table that already has data? +

You must provide a DEFAULT value so the database can populate existing rows. For example: ALTER TABLE users ADD COLUMN country VARCHAR(2) NOT NULL DEFAULT 'US';. After adding the column, you can update specific rows to their correct values, and then optionally drop the default if you want new inserts to always require an explicit value. In PostgreSQL, you can do this in two steps: add the column with a default, then remove the default.

Does SQLite support all ALTER TABLE operations? +

SQLite has historically had very limited ALTER TABLE support. As of SQLite 3.35.0 (2021), it supports DROP COLUMN, and 3.25.0 (2018) added RENAME COLUMN. For anything not supported (like modifying column types), the standard workaround is: create a new table with the desired schema, copy data from the old table, drop the old table, and rename the new one. Tools like Alembic (Python) or Flyway handle this automatically.