ALTER TABLE: Adding, Modifying and Removing Columns

Concept

ALTER TABLE changes the definition of a table that already exists, usually one that already holds data. That is what makes it riskier than CREATE: every change has to be compatible with the rows already stored.

Syntax

ALTER TABLE table_name ADD    COLUMN column_name data_type [constraint];
ALTER TABLE table_name DROP   COLUMN column_name;
ALTER TABLE table_name ADD    CONSTRAINT name constraint_definition;
ALTER TABLE table_name DROP   CONSTRAINT name;

-- Changing a column type is the least portable operation in SQL
ALTER TABLE table_name MODIFY COLUMN column_name new_type;   -- MySQL, Oracle
ALTER TABLE table_name ALTER  COLUMN column_name TYPE new_type; -- PostgreSQL
ALTER TABLE table_name ALTER  COLUMN column_name new_type;   -- SQL Server

Example

-- 1. Add a nullable column: always safe
ALTER TABLE employees ADD COLUMN phone VARCHAR(20) NULL;

-- 2. Add a NOT NULL column: needs a default, or existing rows have no value
ALTER TABLE employees
  ADD COLUMN country CHAR(2) NOT NULL DEFAULT 'IN';

-- 3. Widen a column: safe, no data can be lost
ALTER TABLE employees MODIFY COLUMN last_name VARCHAR(80) NOT NULL;

-- 4. Add a constraint after cleaning the data
UPDATE employees SET salary = 0 WHERE salary < 0;
ALTER TABLE employees
  ADD CONSTRAINT ck_employees_salary CHECK (salary >= 0);

-- 5. Remove a column: destructive and usually irreversible
ALTER TABLE employees DROP COLUMN phone;

Explanation

Step 2 is the one people get wrong. Adding a NOT NULL column to a table with existing rows must supply a value for those rows, otherwise the statement is contradictory and the database rejects it. A DEFAULT solves it. If no sensible default exists, do it in three steps: add the column as nullable, backfill it with UPDATE, then tighten it to NOT NULL.

Step 4 shows the general rule for constraints: the data must already satisfy the rule before the rule can be added.

Renaming

-- Modern MySQL 8, MariaDB 10.5+, PostgreSQL, Oracle
ALTER TABLE employees RENAME COLUMN phone TO contact_phone;
ALTER TABLE employees RENAME TO staff;

-- Older MySQL required the full column definition again
ALTER TABLE employees CHANGE phone contact_phone VARCHAR(20) NULL;

-- SQL Server uses a system stored procedure
EXEC sp_rename 'employees.phone', 'contact_phone', 'COLUMN';

Important rules

• Widening a type is safe; narrowing it can truncate or fail. VARCHAR(50) to VARCHAR(100) is fine, the reverse is not.
• Dropping a column destroys its data. There is no undo without a backup.
• Adding a NOT NULL column to a populated table requires a default or a backfill.
• On large tables ALTER can rewrite the whole table and hold locks. MySQL 8 does many changes online with ALGORITHM=INPLACE, but not all of them.
• You cannot drop a column another object depends on - an index, a view or a foreign key - until that object is dropped or changed.

Common mistakes

• Running an ALTER on a multi million row table during peak hours and locking the application out.
• Adding a UNIQUE constraint before removing duplicate values.
• Renaming a column and forgetting the views, stored procedures and application queries that still use the old name.
• Assuming MODIFY COLUMN keeps the old attributes - in MySQL it replaces the definition, so a forgotten NOT NULL silently becomes nullable.

Best practices

• Ship schema changes as small, reversible migrations, each with a written rollback.
• Test every migration against a copy of production sized data, not an empty table.
• Prefer add-then-backfill-then-tighten over one big statement.
• Deploy in the expand and contract order: add the new column, ship code that writes both, backfill, switch reads, then drop the old column in a later release.

Practice

1. Add a termination_date DATE NULL column to employees, then a CHECK that it is never earlier than hire_date.
2. Write the three step migration that turns the existing nullable employees.email into a NOT NULL UNIQUE column.
3. Why can adding a column with a default be instant in MySQL 8 but slow in an older version?