SQLite Alter Table

The ALTER TABLE statement modifies an existing table without dropping and recreating it. SQLite supports a limited but practical set of alterations compared to other databases. Knowing what SQLite can and cannot alter prevents frustration during database design changes.

What SQLite ALTER TABLE Supports

SUPPORTED IN SQLITE
─────────────────────────────────────────────────
  ✔ Rename the table
  ✔ Rename a column (SQLite 3.25.0+)
  ✔ Add a new column
  ✔ Drop a column (SQLite 3.35.0+)

NOT SUPPORTED DIRECTLY
─────────────────────────────────────────────────
  ✘ Change a column's data type
  ✘ Change column constraints after creation
  ✘ Remove or modify PRIMARY KEY
  ✘ Reorder columns

Rename the Table

Renaming a table changes only the table's name. All data, indexes, and triggers that reference the table update automatically.

-- Original table name: students
-- New table name: learners

ALTER TABLE students RENAME TO learners;

-- Verify
sqlite> .tables
courses   learners   teachers

Rename a Column

Column renaming was added in SQLite version 3.25.0. Use RENAME COLUMN to change a column name while keeping all data intact.

-- Before: column "email" → rename to "contact_email"
ALTER TABLE learners RENAME COLUMN email TO contact_email;

-- Verify
sqlite> .schema learners
CREATE TABLE learners (
  id             INTEGER PRIMARY KEY,
  name           TEXT NOT NULL,
  age            INTEGER,
  contact_email  TEXT
);

Add a New Column

The ADD COLUMN clause appends a new column to an existing table. All existing rows receive the new column with a NULL value (or the default value if specified).

-- Add a phone column to the students table
ALTER TABLE students ADD COLUMN phone TEXT;

-- Add a column with a default value
ALTER TABLE students ADD COLUMN status TEXT DEFAULT 'active';

-- What happens to existing rows:
id │ name  │ age │ phone  │ status
───┼───────┼─────┼────────┼────────
1  │ Alice │ 20  │ NULL   │ active
2  │ Bob   │ 22  │ NULL   │ active

Rules for ADD COLUMN

ALLOWED                          NOT ALLOWED
──────────────────────────────   ──────────────────────────────
Default value allowed            NOT NULL without a default
NULL allowed                     UNIQUE constraint on new col
References to other tables       PRIMARY KEY designation

Drop a Column

Dropping a column removes it and all its data permanently. This works in SQLite 3.35.0 and later.

-- Remove the "age" column from students
ALTER TABLE students DROP COLUMN age;

-- Verify
sqlite> PRAGMA table_info(students);
cid │ name   │ type    │ ...
────┼────────┼─────────┼────
0   │ id     │ INTEGER │ ...
1   │ name   │ TEXT    │ ...
2   │ phone  │ TEXT    │ ...
3   │ status │ TEXT    │ ...
(age column is gone)

Restrictions on DROP COLUMN

CANNOT DROP A COLUMN IF:
──────────────────────────────────────────────────────
  ✘ It is the PRIMARY KEY column
  ✘ It is part of a UNIQUE constraint
  ✘ It is referenced by a foreign key
  ✘ It is referenced in an index
  ✘ It is referenced in a trigger or view

How to Change a Column Type (Workaround)

SQLite does not support changing a column's type directly. The standard approach is to create a new table with the desired structure and copy data into it.

GOAL: Change "age" from TEXT to INTEGER in students

Step 1: Create new table with correct types
CREATE TABLE students_new (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  age   INTEGER,   ← correct type now
  email TEXT
);

Step 2: Copy data from old to new
INSERT INTO students_new
  SELECT id, name, CAST(age AS INTEGER), email
  FROM students;

Step 3: Drop old table
DROP TABLE students;

Step 4: Rename new table
ALTER TABLE students_new RENAME TO students;

Tracking SQLite Version

Some ALTER TABLE features require a minimum SQLite version. Check your version before relying on newer features.

sqlite> SELECT sqlite_version();
3.46.0

Feature availability:
  RENAME COLUMN   → requires 3.25.0+
  DROP COLUMN     → requires 3.35.0+

Key Takeaways

• ALTER TABLE ... RENAME TO renames a table
• ALTER TABLE ... RENAME COLUMN old TO new renames a column (3.25.0+)
• ALTER TABLE ... ADD COLUMN adds a column; existing rows get NULL or the default
• ALTER TABLE ... DROP COLUMN removes a column (3.35.0+)
• To change a column's type, create a new table, copy data, drop old, and rename