Lesson 43 of 61

5 mins read

SQL ALTER TABLE: Modifying Columns & Schema Evolution

In Plain English

Schemas evolve as applications grow. `ALTER TABLE` allows live schema modifications without rebuilding tables from scratch. You can `ADD COLUMN`, `DROP COLUMN`, `RENAME COLUMN`, and `RENAME TO`. In modern databases, adding nullable or default columns is performed in-place with zero downtime.

The Mental Model

"`ALTER TABLE` is like remodeling an existing house by knocking down a wall or adding a room without having to demolish the entire house and rebuild from the ground up."

Deep Dive: How It Works

ADD COLUMN: Appends a new field to existing rows. New columns should be nullable or have sensible defaults.

RENAME COLUMN: Updates field names to reflect new domain naming conventions.

Online Schema Migrations: Modern engines (Postgres/MySQL) perform metadata-only column additions without table locks.

Syntax Blueprint

ALTER TABLE table_name ADD COLUMN column_name datatype;
ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
ALTER TABLE table_name RENAME TO new_table_name;

Modifies the column definitions or entity name of an existing table.

Core Rules to Remember

Non-Destructive Evolution: Adds or updates columns while preserving existing stored records.

Backward Compatibility: Always assign defaults to newly added non-nullable columns.

Locking Considerations: Altering large multi-gigabyte tables in production requires careful planning or online migration tools (gh-ost, pt-online-schema-change).

Common Beginner Traps & How to Fix Them

Mistake:Adding a `NOT NULL` column without a `DEFAULT` value to a table that already contains millions of rows.

Why it happens: Existing rows cannot satisfy the NOT NULL constraint without an explicit default value.

How to fix: Provide a `DEFAULT` value or add the column as nullable first, backfill data, and then apply `NOT NULL`.

Pro Tip: Always run schema migrations inside transactions when supported by your RDBMS (such as PostgreSQL and SQLite).

Live Interactive Example

Hit Run Code to see it live

Adding Columns and Renaming Fields with ALTER TABLE

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: The table schema was modified with an added column and renamed field while preserving all existing row data.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Alter Products Schema

Use `ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;` and select all columns.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.