Chapter 7: Altering and Dropping Structures

Once a database is in use, structures evolve. You add columns, rename objects, create or remove indexes, and carefully roll out changes so applications continue to run. This chapter covers the core statements for structural change; safe patterns for index management; and practical guidance for schema migrations.

ALTER TABLE, RENAME, and DROP TABLE

ALTER TABLE changes an existing table without recreating it. Typical operations include adding or dropping columns, changing data types, setting or removing constraints, and renaming columns or the table itself.

Common ALTER TABLE patterns

-- Add a nullable column with a default (ANSI-style)
ALTER TABLE accounts
  ADD COLUMN last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- Backfill and then enforce NOT NULL (safer two-step)
UPDATE accounts SET last_login = CURRENT_TIMESTAMP WHERE last_login IS NULL;
ALTER TABLE accounts
  ALTER COLUMN last_login SET NOT NULL;           -- PostgreSQL
-- MySQL
ALTER TABLE accounts
  MODIFY last_login TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;
-- SQL Server
ALTER TABLE accounts
  ALTER COLUMN last_login DATETIME2 NOT NULL;

-- Rename a column
-- PostgreSQL / SQLite
ALTER TABLE accounts RENAME COLUMN last_login TO last_seen;
-- MySQL 8+
ALTER TABLE accounts RENAME COLUMN last_login TO last_seen;
-- SQL Server
EXEC sp_rename 'dbo.accounts.last_login', 'last_seen', 'COLUMN';

-- Change a column type (with USING when needed)
-- PostgreSQL
ALTER TABLE orders
  ALTER COLUMN total TYPE NUMERIC(12,2)
  USING total::NUMERIC(12,2);

-- Add a CHECK constraint
ALTER TABLE accounts
  ADD CONSTRAINT chk_age CHECK (age >= 13);

-- Drop a constraint (name varies by engine)
-- PostgreSQL
ALTER TABLE accounts DROP CONSTRAINT chk_age;
-- MySQL (checks are named automatically; use SHOW CREATE TABLE to find the name)
-- SQL Server
ALTER TABLE accounts DROP CONSTRAINT chk_age;

Renaming tables and columns with RENAME

Most engines provide a simple rename that preserves data and constraints.

-- Rename a table
-- PostgreSQL / SQLite
ALTER TABLE session RENAME TO session_archive;

-- MySQL
RENAME TABLE session TO session_archive;

-- SQL Server
EXEC sp_rename 'dbo.session', 'session_archive';

Dropping tables with DROP TABLE

Dropping a table removes its data and metadata. Use conditional clauses to avoid errors if the table may not exist; and use dependency options carefully.

-- Safer: avoid errors if missing
DROP TABLE IF EXISTS temp_import;

-- Handle dependencies
-- PostgreSQL: CASCADE drops dependent objects; RESTRICT fails if any exist
DROP TABLE report CASCADE;
DROP TABLE report RESTRICT;

-- MySQL: CASCADE/RESTRICT are parsed but not enforced for tables; check FKs
DROP TABLE report;

-- SQL Server
DROP TABLE IF EXISTS dbo.report;

Quick reference: common operations and dialect notes

Managing indexes

Indexes accelerate reads by organizing data for fast lookups. Design them for the queries you run; balance read speed against write overhead and storage.

Creating and dropping indexes

-- Basic non-unique index
CREATE INDEX idx_users_email ON users (email);

-- Unique index (enforces no duplicates)
CREATE UNIQUE INDEX uq_users_email ON users (email);

-- Composite (multi-column) index
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

-- Functional / expression index
-- PostgreSQL
CREATE INDEX idx_users_email_lower ON users ((lower(email)));
-- SQLite
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- MySQL (use generated column prior to functional indexes)
ALTER TABLE users ADD COLUMN email_lower VARCHAR(255) GENERATED ALWAYS AS (lower(email)) STORED;
CREATE INDEX idx_users_email_lower ON users (email_lower);

-- Include (covering) columns
-- PostgreSQL 11+
CREATE INDEX idx_orders_cover ON orders (customer_id, order_date) INCLUDE (status);
-- SQL Server
CREATE INDEX idx_orders_cover ON dbo.orders (customer_id, order_date) INCLUDE (status);

-- Partial / filtered index
-- PostgreSQL
CREATE INDEX idx_orders_open ON orders (customer_id) WHERE status = 'OPEN';
-- SQL Server
CREATE INDEX idx_orders_open ON dbo.orders (customer_id) WHERE status = 'OPEN';
-- SQLite supports partial indexes with WHERE
CREATE INDEX idx_orders_open ON orders (customer_id) WHERE status = 'OPEN';

-- Drop indexes (syntax differs)
-- PostgreSQL / SQLite
DROP INDEX IF EXISTS idx_users_email;
-- MySQL
DROP INDEX idx_users_email ON users;
-- SQL Server
DROP INDEX idx_users_email ON dbo.users;

Choosing columns and order

• Place the most selective column first for equality lookups; follow with columns used in join predicates and range filters.
• Create one good composite index instead of many overlapping single-column indexes.
• Cover common queries (all needed columns available by index) using include columns where supported; otherwise consider wider composite keys carefully.
• Prefer expression indexes for case-insensitive search, JSON keys, or computed fields (engine support varies).

Rebuilding and concurrent indexing

-- Build an index without blocking writes (engine-specific)
-- PostgreSQL
CREATE INDEX CONCURRENTLY idx_events_created_at ON events (created_at);

-- Rebuild or reorganize to defragment
-- SQL Server
ALTER INDEX ALL ON dbo.orders REORGANIZE;    -- light
ALTER INDEX ALL ON dbo.orders REBUILD;       -- heavy

Schema migrations

Schema migrations are controlled, versioned changes to database structures applied in sequence. A reliable process reduces risk and keeps environments aligned.

Versioning and tracking

-- A simple migrations ledger
CREATE TABLE schema_migrations (
  version VARCHAR(50) PRIMARY KEY,
  applied_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Mark a migration as applied
INSERT INTO schema_migrations (version) VALUES ('2025-11-02_1200_add_last_seen');

Each migration is an ordered script with an id (for example 2025-11-02_1200_add_last_seen.sql) that can be applied once. Tools like Flyway and Liquibase manage this automatically; the principle is the same.

Designing safe, incremental changes

• Backward compatibility: deploy code that can work with both old and new schema; then migrate the data; then enforce stricter constraints.
• Two-step constraint tightening: add column as nullable; backfill; set NOT NULL; add foreign keys at the end when possible.
• Online operations: use online/instant algorithms when supported (for example MySQL instant add column; PostgreSQL concurrently built indexes; SQL Server online index operations in suitable editions).
• Roll forward: prefer forward fixes over rollbacks; keep rollback scripts only for small reversible steps (for example dropping a new index).

Example migration set

-- 001_expand_add_column.sql
ALTER TABLE accounts
  ADD COLUMN last_seen TIMESTAMP;

-- 002_backfill_last_seen.sql  (run in batches in application code or a job)
UPDATE accounts
SET last_seen = GREATEST(last_login, created_at)
WHERE last_seen IS NULL
  AND id BETWEEN :min_id AND :max_id;

-- 003_enforce_not_null.sql
-- PostgreSQL
ALTER TABLE accounts ALTER COLUMN last_seen SET NOT NULL;
-- MySQL
ALTER TABLE accounts MODIFY last_seen TIMESTAMP NOT NULL;
-- SQL Server
ALTER TABLE accounts ALTER COLUMN last_seen DATETIME2 NOT NULL;

-- 004_add_index.sql
-- PostgreSQL (concurrent)
CREATE INDEX CONCURRENTLY idx_accounts_last_seen ON accounts (last_seen);
-- MySQL
CREATE INDEX idx_accounts_last_seen ON accounts (last_seen);
-- SQL Server
CREATE INDEX idx_accounts_last_seen ON dbo.accounts (last_seen);

-- 005_contract_drop_old.sql
-- After monitoring period
ALTER TABLE accounts DROP COLUMN last_login;

Blue-green and compatibility layers

For renames that risk breaking code, introduce a compatibility layer:

-- Keep old name available during transition
-- PostgreSQL view
CREATE VIEW session AS SELECT * FROM session_archive;

-- SQL Server synonym
CREATE SYNONYM dbo.session FOR dbo.session_archive;

-- MySQL view
CREATE VIEW session AS SELECT * FROM session_archive;

Structure of a migration file

-- <version>__<summary>.sql
-- 2025-11-02__rename_session_to_session_archive.sql

BEGIN;  -- or implicit transaction if the engine handles it

-- … DDL / DML statements here

COMMIT;

Where the engine does not support transactional DDL (or only for some statements), split the migration into safe chunks and ensure idempotency checks are present (for example IF EXISTS, IF NOT EXISTS, or guards in application code).

With careful use of ALTER TABLE, disciplined index management, and versioned migrations, you can evolve a schema smoothly while keeping systems available.