Offensive Security Researcher & Quantum Cryptography Analyst

ALTER TABLE Command in SQLite: A Complete Guide with Examples

Abdul Wahab Junaid
ALTER TABLE Command in SQLite

Schemas rarely stay the same forever. At some point you’ll need to add a column you forgot, rename a table to better reflect what it actually holds, or clean up a column you no longer use. In most databases, ALTER TABLE is the Swiss Army knife for all of this. SQLite’s version of ALTER TABLE, though, is intentionally more limited than what you’d find in MySQL or PostgreSQL, and understanding exactly where those limits sit will save you a lot of frustration.

In this guide, I’ll cover everything ALTER TABLE can and can’t do in SQLite, the correct syntax for each supported operation, and the standard workaround pattern for changes SQLite doesn’t support directly.

What ALTER TABLE Does

ALTER TABLE modifies the structure of an existing table without requiring you to drop and manually recreate it from scratch (at least for the operations it supports). SQLite supports the following ALTER TABLE operations:

That’s it. Notably absent compared to other databases: you cannot change a column’s data type, you cannot add or remove constraints like NOT NULL or CHECK directly, you cannot reorder columns, and you cannot modify a PRIMARY KEY or FOREIGN KEY definition through ALTER TABLE. For any of those changes, you need to use the table-rebuild pattern I’ll cover later in this article.

Renaming a Table

The basic syntax for renaming a table is:

ALTER TABLE old_table_name RENAME TO new_table_name;

For example:

ALTER TABLE customer_data RENAME TO customers;

This is a lightweight operation — SQLite just updates its internal schema catalog, so it’s fast even on large tables. One thing worth knowing: SQLite will also automatically update references to the renamed table inside any views, triggers, or foreign key definitions that reference it, as of relatively recent SQLite versions (3.25.0 and later handled this more gracefully; older versions had some rough edges around updating dependent objects, so if you’re on an older SQLite build, it’s worth double-checking that dependent triggers and views still work correctly after a rename).

Renaming a Column

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

For example:

ALTER TABLE customers RENAME COLUMN email_address TO email;

This feature was added in SQLite 3.25.0 (released in 2018), so if you’re working with an unusually old SQLite installation, this specific syntax might not be available — though at this point, most systems ship with a version well past that. Like renaming a table, SQLite attempts to update references in views, triggers, and other dependent schema objects automatically, though it’s always good practice to verify this worked correctly in your specific case, especially with more complex trigger logic.

Adding a Column

ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

A straightforward example:

ALTER TABLE customers ADD COLUMN phone_number TEXT;

You can include a default value:

ALTER TABLE customers ADD COLUMN loyalty_points INTEGER DEFAULT 0;

There are a few important restrictions to keep in mind when adding columns:

-- This works: NOT NULL paired with a DEFAULT value
ALTER TABLE customers ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

-- This fails: NOT NULL with no default, since existing rows would have no value
ALTER TABLE customers ADD COLUMN status TEXT NOT NULL;

You can also add a foreign key column, but be aware that SQLite doesn’t validate existing data against the new foreign key constraint automatically when you add it this way — it’s mostly enforced going forward, so if data integrity matters here, double check your data manually or through a follow-up query.

Dropping a Column

Support for dropping a column was added later than the other operations, in SQLite 3.35.0 (released in 2021). The syntax is:

ALTER TABLE table_name DROP COLUMN column_name;

For example:

ALTER TABLE customers DROP COLUMN phone_number;

There are a handful of restrictions here too:

If you’re running an older version of SQLite (before 3.35.0), DROP COLUMN simply isn’t available, and you’ll need to use the table-rebuild workaround described below.

Checking Your SQLite Version

Since several ALTER TABLE features depend on version, it’s worth checking what you’re actually running before assuming a feature is available:

SELECT sqlite_version();

If you’re on an older bundled version (common in some mobile app frameworks or older Python distributions using the sqlite3 standard library), some of the syntax above may not work, and you’ll need the manual workaround.

What ALTER TABLE Cannot Do

To be direct about it, here’s what you cannot do with ALTER TABLE in SQLite, regardless of version:

If you need any of these, SQLite expects you to use the standard 12-step table-rebuild pattern that its own documentation recommends.

The Table-Rebuild Workaround

Since so many schema changes fall outside what ALTER TABLE directly supports, SQLite’s own documentation lays out a well-established pattern for making any structural change safely: create a new table with the desired structure, copy the data over, drop the old table, and rename the new one into place.

Here’s a full example of changing a column’s data type (something ALTER TABLE flatly cannot do) — say, changing age from TEXT to INTEGER:

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

-- Step 1: Create the new table with the desired structure
CREATE TABLE customers_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

-- Step 2: Copy data across, casting as needed
INSERT INTO customers_new (id, name, age)
SELECT id, name, CAST(age AS INTEGER) FROM customers;

-- Step 3: Drop the old table
DROP TABLE customers;

-- Step 4: Rename the new table into place
ALTER TABLE customers_new RENAME TO customers;

COMMIT;

PRAGMA foreign_keys=ON;

A few important details about this pattern:

Adding a CHECK Constraint via Rebuild

Since ALTER TABLE can’t add constraints, here’s how you’d add a CHECK constraint to an existing table:

BEGIN TRANSACTION;

CREATE TABLE products_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL CHECK (price >= 0)
);

INSERT INTO products_new SELECT * FROM products;

DROP TABLE products;

ALTER TABLE products_new RENAME TO products;

COMMIT;

Note that if any existing rows violate the new constraint (say, a negative price already exists in the data), this INSERT step will fail, and you’ll need to clean up the offending data first before attempting the rebuild.

Common Use Cases

Best Practices

  1. Always back up your database before running structural changes, especially anything involving the rebuild pattern, since a mistake mid-migration can be hard to fully undo.
  2. Wrap ALTER TABLE and rebuild operations in transactions so failures don’t leave your schema half-changed.
  3. Check your SQLite version before relying on RENAME COLUMN or DROP COLUMN syntax, since both were added in specific, relatively recent releases.
  4. Pair NOT NULL columns with a DEFAULT value when adding them to a table that already has rows.
  5. Remember to recreate indexes and triggers after using the table-rebuild pattern, since they aren’t automatically carried over.
  6. Disable and re-enable foreign key checks around rebuild operations if other tables reference the one being restructured.
  7. Test schema migrations on a copy of production data before running them for real, particularly for anything involving type casting, since unexpected data can cause the migration to fail partway through.

Wrapping Up

SQLite’s ALTER TABLE command covers the basics well — renaming tables, renaming columns, adding columns, and (in modern versions) dropping columns — but it deliberately stops short of the full range of schema changes you might be used to from larger database systems. For anything beyond that, from changing data types to adding constraints, the standard approach is the create-copy-drop-rename pattern, which, once you’ve done it a couple of times, becomes a fairly routine and reliable way to reshape a table safely. Knowing this distinction upfront — what ALTER TABLE handles directly versus what needs the rebuild workaround — will save you a lot of trial and error the next time your schema needs to grow.

Exit mobile version