SQL - ALTER TABLE (Add/Modify/Drop Column)

Key Insights

ALTER TABLE commands modify existing table structures without data loss, supporting column additions, modifications, and deletions across major databases
Syntax varies significantly between MySQL, PostgreSQL, SQL Server, and Oracle—understanding vendor-specific implementations prevents production errors
Column modifications require careful consideration of existing data, constraints, and indexes to avoid integrity violations or performance degradation

Adding Columns to Existing Tables

Adding columns is the most common ALTER TABLE operation. The basic syntax is straightforward, but production implementations require attention to default values and nullability.

-- Basic column addition
ALTER TABLE employees
ADD email VARCHAR(255);

-- Add column with constraints
ALTER TABLE employees
ADD hire_date DATE NOT NULL DEFAULT CURRENT_DATE;

-- Add multiple columns (MySQL, PostgreSQL)
ALTER TABLE employees
ADD COLUMN department_id INT,
ADD COLUMN manager_id INT;

When adding NOT NULL columns to tables with existing data, you must provide a DEFAULT value or the operation fails. Without a default, the database cannot populate existing rows.

-- This fails if table has data
ALTER TABLE employees
ADD salary DECIMAL(10,2) NOT NULL;

-- Correct approach
ALTER TABLE employees
ADD salary DECIMAL(10,2) NOT NULL DEFAULT 0.00;

-- Or add as nullable first, populate, then modify
ALTER TABLE employees
ADD salary DECIMAL(10,2);

UPDATE employees SET salary = 50000 WHERE salary IS NULL;

ALTER TABLE employees
MODIFY salary DECIMAL(10,2) NOT NULL;

Database-Specific ADD Column Syntax

Each database system has quirks in ALTER TABLE syntax that matter in production environments.

PostgreSQL requires the COLUMN keyword for clarity:

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Add with check constraint
ALTER TABLE employees
ADD COLUMN age INT CHECK (age >= 18 AND age <= 100);

SQL Server uses slightly different syntax for constraints:

ALTER TABLE employees
ADD email VARCHAR(255) NOT NULL
CONSTRAINT DF_employees_email DEFAULT 'noemail@company.com';

-- Add computed column
ALTER TABLE employees
ADD full_name AS (first_name + ' ' + last_name);

Oracle supports adding columns with inline constraints:

ALTER TABLE employees
ADD (
    email VARCHAR2(255) NOT NULL,
    phone VARCHAR2(20),
    CONSTRAINT uk_employee_email UNIQUE (email)
);

Modifying Existing Columns

Column modifications are more complex than additions because they affect existing data. The operation’s success depends on data compatibility with the new definition.

MySQL uses MODIFY or CHANGE:

-- MODIFY changes column definition
ALTER TABLE employees
MODIFY email VARCHAR(320) NOT NULL;

-- CHANGE renames and modifies
ALTER TABLE employees
CHANGE email email_address VARCHAR(320) NOT NULL;

-- Change data type
ALTER TABLE employees
MODIFY employee_id BIGINT;

PostgreSQL uses ALTER COLUMN with specific actions:

-- Change data type
ALTER TABLE employees
ALTER COLUMN email TYPE VARCHAR(320);

-- Set/drop NOT NULL
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

ALTER TABLE employees
ALTER COLUMN phone DROP NOT NULL;

-- Change default value
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'active';

-- Multiple modifications
ALTER TABLE employees
ALTER COLUMN email TYPE VARCHAR(320),
ALTER COLUMN email SET NOT NULL;

SQL Server uses ALTER COLUMN:

ALTER TABLE employees
ALTER COLUMN email VARCHAR(320) NOT NULL;

-- Change to nullable
ALTER TABLE employees
ALTER COLUMN phone VARCHAR(20) NULL;

Handling Data Type Conversions

Converting column types requires understanding implicit conversion rules and potential data loss scenarios.

-- Safe conversions (widening)
ALTER TABLE products
ALTER COLUMN price DECIMAL(12,2); -- from DECIMAL(10,2)

-- Risky conversions (narrowing) - check data first
SELECT MAX(LENGTH(description)) FROM products;

ALTER TABLE products
ALTER COLUMN description VARCHAR(500); -- from TEXT

-- Type conversion with data validation
-- PostgreSQL USING clause
ALTER TABLE orders
ALTER COLUMN order_date TYPE TIMESTAMP
USING order_date::TIMESTAMP;

-- Convert string to integer with validation
ALTER TABLE products
ALTER COLUMN sku TYPE INTEGER
USING sku::INTEGER;

For complex conversions, create a new column, migrate data, then swap:

-- Add new column
ALTER TABLE employees
ADD email_new VARCHAR(320);

-- Migrate and validate data
UPDATE employees
SET email_new = LOWER(TRIM(email))
WHERE email IS NOT NULL;

-- Verify migration
SELECT COUNT(*) FROM employees WHERE email IS NOT NULL AND email_new IS NULL;

-- Drop old column and rename
ALTER TABLE employees DROP COLUMN email;
ALTER TABLE employees RENAME COLUMN email_new TO email;

Dropping Columns

Dropping columns is destructive and irreversible. Always backup data and verify dependencies before dropping columns in production.

-- Basic drop
ALTER TABLE employees
DROP COLUMN middle_name;

-- Drop multiple columns (MySQL, PostgreSQL)
ALTER TABLE employees
DROP COLUMN fax,
DROP COLUMN pager;

-- SQL Server syntax
ALTER TABLE employees
DROP COLUMN fax, pager;

Columns with constraints or indexes may require CASCADE options:

-- PostgreSQL with CASCADE
ALTER TABLE employees
DROP COLUMN department_id CASCADE;

-- MySQL drops dependent foreign keys automatically
ALTER TABLE employees
DROP COLUMN department_id;

-- SQL Server requires manual constraint removal
ALTER TABLE employees
DROP CONSTRAINT FK_employees_departments;

ALTER TABLE employees
DROP COLUMN department_id;

Managing Constraints During Modifications

Constraints often block column modifications. You must drop constraints, modify columns, then recreate constraints.

-- Check existing constraints
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';

-- Drop constraint, modify, recreate
ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;

ALTER TABLE employees
ALTER COLUMN salary DECIMAL(12,2);

ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive CHECK (salary > 0);

For foreign key constraints:

-- Identify foreign keys
SELECT constraint_name
FROM information_schema.key_column_usage
WHERE table_name = 'orders' AND column_name = 'customer_id';

-- Drop FK, modify column, recreate FK
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customers;

ALTER TABLE orders
MODIFY customer_id BIGINT;

ALTER TABLE customers
MODIFY customer_id BIGINT;

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

Performance Considerations

ALTER TABLE operations can lock tables and impact production systems. Understanding locking behavior is critical.

MySQL (pre-8.0) often requires table rebuilds:

-- Instant operations (MySQL 8.0+)
ALTER TABLE employees
ADD COLUMN status VARCHAR(20) DEFAULT 'active',
ALGORITHM=INSTANT;

-- Online DDL
ALTER TABLE employees
MODIFY email VARCHAR(320),
ALGORITHM=INPLACE, LOCK=NONE;

PostgreSQL handles most additions without rewrites:

-- Fast: adding nullable column with no default
ALTER TABLE employees
ADD COLUMN notes TEXT;

-- Slow: adding NOT NULL with default (pre-11)
-- Fast in PostgreSQL 11+ with volatile defaults
ALTER TABLE employees
ADD COLUMN created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

For large tables, consider these strategies:

-- Add column as nullable first
ALTER TABLE large_table ADD COLUMN new_col VARCHAR(100);

-- Batch update in chunks
UPDATE large_table SET new_col = 'default'
WHERE id BETWEEN 1 AND 100000;

-- Then add NOT NULL constraint
ALTER TABLE large_table ALTER COLUMN new_col SET NOT NULL;

Always test ALTER TABLE operations on production-sized datasets in staging environments to estimate execution time and locking impact.