SQL ALTER TABLE

The ALTER TABLE statement modifies the structure of an existing table without touching the data inside it (unless you’re dropping a column or changing a data type incompatibly). It’s the primary tool for evolving database schemas over time.

Adding a Column

-- Basic add
ALTER TABLE customers ADD COLUMN phone VARCHAR(20);

-- Add with a default value (existing rows get the default)
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- Add a NOT NULL column (requires a default or backfill)
ALTER TABLE products ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;

When you add a NOT NULL column without a default, the database needs a value for all existing rows immediately. Either:

Provide a DEFAULT (simplest)
Add the column as nullable, backfill it, then add the constraint

For very large tables, adding a column with a default can be slow or even lock the table in older PostgreSQL versions. PostgreSQL 11+ handles this efficiently for constant defaults.

Modifying a Column

Change data type:

-- PostgreSQL
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12, 2);

-- MySQL
ALTER TABLE products MODIFY COLUMN price DECIMAL(12, 2);

-- SQL Server
ALTER TABLE products ALTER COLUMN price DECIMAL(12, 2);

Data type changes only succeed if existing data can be converted. Changing VARCHAR to INTEGER fails if any rows contain non-numeric text. Cast explicitly when needed:

-- PostgreSQL: cast during type change
ALTER TABLE events
    ALTER COLUMN event_timestamp TYPE TIMESTAMP
    USING event_timestamp::TIMESTAMP;

Change a column’s default:

-- PostgreSQL
ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;

-- MySQL
ALTER TABLE orders MODIFY COLUMN status VARCHAR(20) DEFAULT 'pending';

Renaming

Rename a column:

-- PostgreSQL, SQL Server 2022+, SQLite 3.25+
ALTER TABLE customers RENAME COLUMN phone TO phone_number;

-- MySQL
ALTER TABLE customers RENAME COLUMN phone TO phone_number;

-- SQL Server (older versions use sp_rename)
EXEC sp_rename 'customers.phone', 'phone_number', 'COLUMN';

Rename a table:

ALTER TABLE old_table_name RENAME TO new_table_name;

Dropping a Column

ALTER TABLE customers DROP COLUMN middle_name;

-- PostgreSQL: cascade drops dependent objects (views, constraints)
ALTER TABLE customers DROP COLUMN middle_name CASCADE;

Dropping a column is irreversible. Data in that column is gone. In production, a safer sequence is:

Stop writing to the column in application code
Monitor for any remaining reads
Then drop the column

Adding and Dropping Constraints

Add a primary key:

ALTER TABLE orders ADD PRIMARY KEY (order_id);

Add a foreign key:

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

Add a unique constraint:

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Add a check constraint:

ALTER TABLE products
    ADD CONSTRAINT chk_positive_price CHECK (price > 0);

Drop a constraint:

-- PostgreSQL, SQL Server
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;

-- MySQL (different syntax per constraint type)
ALTER TABLE orders DROP FOREIGN KEY fk_orders_customer;
ALTER TABLE products DROP CHECK chk_positive_price;

Adding Indexes via ALTER TABLE

In most databases, indexes are created with CREATE INDEX, not ALTER TABLE. But MySQL/MariaDB support it:

-- MySQL
ALTER TABLE orders ADD INDEX idx_orders_date (order_date);
ALTER TABLE orders ADD INDEX idx_composite (customer_id, order_date);

For PostgreSQL and SQL Server, use CREATE INDEX:

-- PostgreSQL (CONCURRENTLY avoids locking the table)
CREATE INDEX CONCURRENTLY idx_orders_date ON orders(order_date);

Multiple Changes in One Statement

Most databases allow multiple changes in a single ALTER TABLE:

-- PostgreSQL
ALTER TABLE products
    ADD COLUMN sku VARCHAR(50),
    ADD COLUMN weight_kg NUMERIC(8, 3),
    ALTER COLUMN price TYPE NUMERIC(12, 2),
    DROP COLUMN legacy_code;

-- MySQL
ALTER TABLE products
    ADD COLUMN sku VARCHAR(50) AFTER name,
    ADD COLUMN weight_kg DECIMAL(8, 3),
    MODIFY COLUMN price DECIMAL(12, 2),
    DROP COLUMN legacy_code;

Batching changes into a single ALTER TABLE is more efficient than running separate statements, because many databases rebuild the table structure once rather than once per statement.

Schema Changes in Production: Safe Practices

Schema changes on live, high-traffic tables require care. Key patterns:

Expand-contract for column renames:

Add the new column alongside the old one
Dual-write to both columns in application code
Backfill old data into the new column
Switch reads to the new column
Drop the old column

This allows zero-downtime renames without locking the table for the entire migration.

PostgreSQL 12+ generated columns:

-- Computed column that stays in sync automatically
ALTER TABLE orders
    ADD COLUMN total_with_tax NUMERIC(10, 2)
    GENERATED ALWAYS AS (amount * 1.1) STORED;

Check table size before altering:

-- PostgreSQL
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables
WHERE tablename = 'orders';

For tables over ~1GB, plan your ALTER TABLE carefully — some operations lock the table and can cause downtime. Tools like pg_repack or pt-online-schema-change help with large table migrations.

Summary: ALTER TABLE Operations

ALTER TABLE operations quick reference

  Add column:         ADD COLUMN name type [DEFAULT val] [NOT NULL]
  Drop column:        DROP COLUMN name [CASCADE]
  Rename column:      RENAME COLUMN old TO new
  Change type:        ALTER COLUMN name TYPE new_type [USING expr]
  Set default:        ALTER COLUMN name SET DEFAULT value
  Drop default:       ALTER COLUMN name DROP DEFAULT
  Add NOT NULL:       ALTER COLUMN name SET NOT NULL
  Drop NOT NULL:      ALTER COLUMN name DROP NOT NULL
  Add constraint:     ADD CONSTRAINT name type (columns)
  Drop constraint:    DROP CONSTRAINT name
  Rename table:       RENAME TO new_name