What Is ALTER TABLE?

-- Basic syntax
ALTER TABLE table_name
ADD | DROP | ALTER | RENAME ...

Adding Columns

-- Add a single column
ALTER TABLE employees
ADD COLUMN phone VARCHAR(20);

-- Add multiple columns (PostgreSQL, MySQL)
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50),
ADD COLUMN suffix VARCHAR(10),
ADD COLUMN hire_date DATE NOT NULL DEFAULT CURRENT_DATE;

-- Add column with a constraint
ALTER TABLE employees
ADD COLUMN age INTEGER CHECK (age >= 18);

-- SQL Server: Add column (no COLUMN keyword)
ALTER TABLE employees
ADD phone VARCHAR(20);

Dropping Columns

-- Drop a single column
ALTER TABLE employees
DROP COLUMN suffix;

-- Drop multiple columns
ALTER TABLE employees
DROP COLUMN middle_name,
DROP COLUMN suffix;

-- Drop column only if it exists (PostgreSQL)
ALTER TABLE employees
DROP COLUMN IF EXISTS suffix;

-- SQL Server: Drop column only if it exists
IF EXISTS (SELECT * FROM sys.columns WHERE name = 'suffix' AND object_id = OBJECT_ID('employees'))
    ALTER TABLE employees
    DROP COLUMN suffix;

-- Drop column with CASCADE (removes dependent objects)
ALTER TABLE employees
DROP COLUMN employee_id CASCADE;

Renaming Columns

-- PostgreSQL and MySQL
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;

-- SQL Server
EXEC sp_rename 'employees.phone', 'phone_number', 'COLUMN';

-- Oracle
ALTER TABLE employees
RENAME COLUMN phone TO phone_number;

Changing Column Data Types

-- Change data type (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN phone TYPE VARCHAR(30);

-- Change data type (MySQL)
ALTER TABLE employees
MODIFY COLUMN phone VARCHAR(30);

-- SQL Server: Change data type
ALTER TABLE employees
ALTER COLUMN phone VARCHAR(30);

-- Change column type with default (MySQL)
ALTER TABLE employees
MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'Active';

Adding and Dropping Constraints

Adding Constraints

-- Add NOT NULL constraint
ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

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

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

-- Add FOREIGN KEY constraint
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Add PRIMARY KEY (if table doesn't have one)
ALTER TABLE employees
ADD CONSTRAINT pk_employees PRIMARY KEY (employee_id);

Dropping Constraints

-- Drop a named constraint
ALTER TABLE employees
DROP CONSTRAINT chk_salary_positive;

-- Drop NOT NULL constraint (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

-- Drop PRIMARY KEY (MySQL)
ALTER TABLE employees
DROP PRIMARY KEY;

-- Drop FOREIGN KEY (MySQL — need to drop index too)
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customer;

Modifying DEFAULT Values

-- Set a new default (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'Active';

-- Remove a default (PostgreSQL)
ALTER TABLE employees
ALTER COLUMN status DROP DEFAULT;

-- Set default (SQL Server)
ALTER TABLE employees
ADD CONSTRAINT df_status DEFAULT 'Active' FOR status;

-- Set default (MySQL)
ALTER TABLE employees
ALTER COLUMN status SET DEFAULT 'Active';

Renaming Tables

-- PostgreSQL and MySQL
ALTER TABLE employees
RENAME TO staff;

-- SQL Server
EXEC sp_rename 'employees', 'staff';

-- Oracle
RENAME employees TO staff;

Complete Table Migration Example

-- Start with the original table
-- employees: id, name, dept, salary

-- Step 1: Add missing columns
ALTER TABLE employees
ADD COLUMN email VARCHAR(100),
ADD COLUMN phone VARCHAR(20),
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

-- Step 2: Rename for consistency
ALTER TABLE employees
RENAME COLUMN name TO full_name;

-- Step 3: Add constraints
ALTER TABLE employees
ADD CONSTRAINT uq_emp_email UNIQUE (email),
ALTER COLUMN email SET NOT NULL,
ADD CONSTRAINT chk_emp_salary CHECK (salary > 0);

-- Step 4: Drop obsolete column
ALTER TABLE employees
DROP COLUMN dept;

-- Step 5: Change type for precision
ALTER TABLE employees
ALTER COLUMN salary TYPE DECIMAL(12,2);

ALTER TABLE Operations by Database

Performance Considerations

Common Pitfalls

Best Practices