Altering Tables

Real schemas evolve.

SQLite supports several ALTER TABLE operations directly, while some structural changes still require a table rebuild pattern.

Core Concepts

flowchart TD
  A[Need schema change] --> B{Supported by ALTER TABLE?}
  B -->|Yes| C[Run direct ALTER]
  B -->|No| D[Rebuild table pattern]
  C --> E[Verify with .schema]
  D --> E

Operation	Direct support in modern SQLite
Rename table	Yes
Rename column	Yes
Add column	Yes
Drop column	Yes (SQLite 3.35+)

Direct ALTER Examples

-- Add a new optional column.
ALTER TABLE employees
ADD COLUMN department TEXT;

Expected output
Column is added to employees (no result rows).

-- Rename an existing column.
ALTER TABLE employees
RENAME COLUMN department TO team;

Expected output
Column name updates from department to team.

Verify Changes

-- Confirm new table definition.
.schema employees

Expected output
CREATE TABLE employees (...) reflects altered columns.

Code Examples

-- Example setup for experimentation.
CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL
);

Expected output
Table employees created successfully.

-- Add a nullable column for incremental evolution.
ALTER TABLE employees
ADD COLUMN hire_date TEXT;

Expected output
hire_date column is added.

SQLite-Specific Nuances

SQLite Nuance

SQLite's ALTER TABLE feature set has grown over time.

If you rely on newer operations (like DROP COLUMN), verify your SQLite version.

Common Pitfalls / Best Practices

Pitfall

Running table-alter operations in production-like data without first checking the resulting schema.

Best Practice

After every structural change, run .schema table_name and store migration SQL in version control.

Quick Challenge

Given projects(project_id INTEGER PRIMARY KEY, name TEXT), add a nullable deadline column.

View Solution

ALTER TABLE projects
ADD COLUMN deadline TEXT;

Expected output
deadline appears in .schema projects.