SQL ALTER TABLE for Schema Changes

SQL ALTER TABLE for Schema Changes

The ALTER TABLE statement modifies an existing table structure. It is the primary tool for evolving database schemas as application requirements change.

Adding Columns

ALTER TABLE employees
ADD COLUMN phone VARCHAR(20),
ADD COLUMN is_active BOOLEAN DEFAULT true NOT NULL;

New columns are added with NULL values for existing rows unless a DEFAULT is specified.

Modifying Columns

-- PostgreSQL
ALTER TABLE products
ALTER COLUMN price TYPE DECIMAL(12, 2),
ALTER COLUMN description SET NOT NULL;

-- MySQL
ALTER TABLE products
MODIFY COLUMN price DECIMAL(12, 2),
MODIFY COLUMN description TEXT NOT NULL;

Dropping Columns

ALTER TABLE users
DROP COLUMN IF EXISTS legacy_field;

Renaming

-- Rename column (PostgreSQL)
ALTER TABLE employees RENAME COLUMN dept TO department;

-- Rename table
ALTER TABLE user_accounts RENAME TO accounts;

Managing Constraints

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

-- Drop constraint
ALTER TABLE orders DROP CONSTRAINT fk_customer;

-- Add index
CREATE INDEX idx_orders_date ON orders(order_date);

Zero-Downtime Migrations

For production databases, schema changes must be planned carefully:

1. Add nullable columns first, then backfill data, then add NOT NULL constraint
2. Never rename columns directly in production; add the new column, copy data, update application code, then drop the old column
3. Add indexes concurrently where supported (CREATE INDEX CONCURRENTLY in PostgreSQL)
4. Test migrations on a copy of production data to estimate execution time
5. Use small batches for data backfills to avoid long-running locks

Locking Considerations

• Adding a column with a DEFAULT is instant in PostgreSQL 11+ and MySQL 8.0+
• Adding a NOT NULL constraint requires scanning all rows
• Creating an index locks the table in MySQL unless using ALGORITHM=INPLACE

ALTER TABLE is essential for database evolution, but requires careful planning to avoid downtime and data loss in production environments.