SQL — Alter Table

What is ALTER TABLE?

ALTER TABLE modifies an existing table's structure — adding columns, changing data types, renaming columns, or adding constraints.

Add a Column

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

Add Multiple Columns

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN address TEXT,
ADD COLUMN hire_date DATE;

Drop a Column

ALTER TABLE employees
DROP COLUMN phone;

Modify a Column

Change the data type or constraints:

-- MySQL
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(25);

-- PostgreSQL
ALTER TABLE employees
ALTER COLUMN phone TYPE VARCHAR(25);

Rename a Column

-- MySQL
ALTER TABLE employees
CHANGE COLUMN phone telephone VARCHAR(20);

-- PostgreSQL / SQL Server
ALTER TABLE employees
RENAME COLUMN phone TO telephone;

Rename a Table

ALTER TABLE employees
RENAME TO staff;

Add a Constraint

-- Add NOT NULL
ALTER TABLE employees
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

-- Add CHECK
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

-- Add DEFAULT
ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

Drop a Constraint

-- MySQL: need the constraint name
ALTER TABLE employees
DROP INDEX unique_email;

-- PostgreSQL / SQL Server
ALTER TABLE employees
DROP CONSTRAINT unique_email;

Add a Foreign Key

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(id);

Practical Example: Evolving a Schema

-- Start simple
CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

-- Add fields as the app grows
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;
ALTER TABLE users ADD UNIQUE (email);
ALTER TABLE users ADD COLUMN created_at DATETIME DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE users ADD COLUMN bio TEXT;

Best Practices

• Back up your table before major structural changes
• Use IF EXISTS where supported to avoid errors
• Test schema changes on a staging database first
• Large ALTER TABLE operations can lock the table — plan for downtime on production
• Use migration tools (Flyway, Liquibase) to track schema changes in version control

Practice

1. Add a department column to an employees table
2. Change the data type of a column from INT to BIGINT
3. Add a unique constraint to an email column
4. Drop a column that's no longer needed