SQL ALTER TABLE: Add, Modify, Drop Columns & Schema Evolution

In production software systems, requirements evolve continuously. You frequently need to add phone numbers, expand character limits, rename columns, or attach new foreign keys to existing tables without destroying existing customer data. The ALTER TABLE DDL statement enables safe schema evolution and migrations.

1. Adding New Columns (ADD COLUMN)

To add one or more new columns to an existing table:

-- Add a single phone_number column
ALTER TABLE students
ADD COLUMN phone_number VARCHAR(15) AFTER email;

-- Add multiple columns in a single statement
ALTER TABLE students
ADD COLUMN emergency_contact VARCHAR(100),
ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

2. Modifying Column Types & Constraints (MODIFY / ALTER)

When you need to expand field capacity (e.g., increasing an email field from 50 to 120 characters) or change default values:

-- MySQL Syntax: Expand email column size and enforce NOT NULL
ALTER TABLE students
MODIFY COLUMN email VARCHAR(120) NOT NULL;

-- PostgreSQL Syntax: Alter column data type
ALTER TABLE students
ALTER COLUMN email TYPE VARCHAR(120);

3. Renaming Columns & Tables (RENAME)

Standard ANSI SQL supports renaming columns without data migration scripts:

-- Rename column from 'gpa' to 'grade_point_average'
ALTER TABLE students
RENAME COLUMN gpa TO grade_point_average;

-- Rename the entire table
ALTER TABLE students
RENAME TO university_students;

4. Dropping Columns (DROP COLUMN)

To remove an obsolete column and free physical disk space:

-- Permanently remove emergency_contact column
ALTER TABLE students
DROP COLUMN emergency_contact;

5. Adding & Dropping Table Constraints

You can attach constraints (like primary keys, foreign keys, or unique rules) to tables after creation:

-- Add a named UNIQUE constraint to phone_number
ALTER TABLE students
ADD CONSTRAINT unique_phone UNIQUE (phone_number);

-- Drop the constraint later if business rules change
ALTER TABLE students
DROP INDEX unique_phone; -- MySQL
-- ALTER TABLE students DROP CONSTRAINT unique_phone; -- PostgreSQL/Oracle

Next, let us understand the critical differences between DROP vs TRUNCATE vs DELETE!