SQL ALTER TABLE

The ALTER TABLE statement changes the structure of a table that already exists, without forcing you to drop it and start over. With it you can add a new column as your data model grows, rename a column or an entire table when requirements shift, or remove a column you no longer need. Because production tables can hold millions of rows, understanding exactly what ALTER TABLE does under the hood — and what it flatly refuses to do — is essential before you run it against anything that matters.

Overview: How ALTER TABLE Works

Every table’s structure lives in the database engine’s schema catalog — in SQLite this is the hidden sqlite_master (also called sqlite_schema) table; in PostgreSQL, MySQL, and SQL Server it’s a set of system views such as information_schema.columns. When you run ALTER TABLE, the engine’s first job is to validate the request against that catalog: does the table exist, does the column you’re adding already exist, does the column you’re dropping or renaming actually exist. Only after that validation does it decide how to apply the change.

Adding a column is normally cheap. Modern engines — SQLite, PostgreSQL 11+, and MySQL 8.0 with its “instant DDL” — treat ADD COLUMN as a metadata-only operation. The engine doesn’t have to touch a single existing row on disk; instead, it records the new column and its default in the schema, and when you later query an old row, the engine simply supplies the default (or NULL) for the column that row doesn’t physically store. That’s why you can add a column to a billion-row table almost instantly.

Dropping a column used to be far more expensive. Historically, SQLite had no DROP COLUMN support at all, and older versions of some engines had to rewrite the entire table to remove a column. Since SQLite 3.35.0 (2021), DROP COLUMN is also a fast, metadata-only change — but only when the column isn’t referenced by a PRIMARY KEY, a UNIQUE or CHECK constraint, an index, a foreign key, or a generated column expression. If any of those apply, SQLite refuses the statement outright rather than silently rewriting your schema, and you have to remove the dependent object first or rebuild the table (covered in Common Mistakes below).

Renaming a column or a table is likewise a metadata-only operation in SQLite: the engine updates the schema entry and automatically fixes up any views, triggers, and foreign keys that referenced the old name. The one thing SQLite’s ALTER TABLE will not do, in any version, is change a column’s declared data type. Other engines expose this directly — PostgreSQL’s ALTER COLUMN ... TYPE, MySQL’s MODIFY COLUMN, SQL Server’s ALTER COLUMN — but internally they often have to rewrite every row anyway, since each existing value must be checked or converted to the new type. SQLite instead expects you to do that rewrite explicitly: create a new table with the type you want, copy the data across (casting it as needed), drop the old table, and rename the new one into place.

Syntax

The general forms of ALTER TABLE supported by SQLite (and, with minor dialect differences, by most other engines) are:

Form What it does
ALTER TABLE table_name ADD COLUMN column_name datatype [DEFAULT value]; Adds a new column, optionally with a default value applied to existing rows
ALTER TABLE table_name DROP COLUMN column_name; Removes an existing column, if it isn’t used by a key, index, or constraint
ALTER TABLE table_name RENAME COLUMN old_name TO new_name; Renames a column without changing its data or type
ALTER TABLE table_name RENAME TO new_table_name; Renames the entire table

Note the dialect differences: MySQL uses MODIFY COLUMN or CHANGE COLUMN to alter a column’s definition in place, PostgreSQL and SQL Server use ALTER COLUMN ... TYPE / ALTER COLUMN ... datatype, and none of those forms are recognized by SQLite at all.

Examples

Example 1: Adding a column

ALTER TABLE employees ADD COLUMN email TEXT;
SELECT id, name, email FROM employees;

Result: all 4 rows from employees come back with their existing id and name values unchanged, plus the new email column showing NULL for every row, because no data has been supplied for it yet.

This is the simplest and most common use of ALTER TABLE. Because no DEFAULT was specified, SQLite fills the column with NULL for every pre-existing row, and any new row inserted afterward will also default to NULL unless you supply a value explicitly.

Example 2: Renaming a column

ALTER TABLE employees RENAME COLUMN department TO division;
SELECT id, name, division FROM employees;

Result: the same 4 rows come back, with id and name unchanged; the column that used to be called department now appears as division, but every row still holds the same value it held before (Engineering, Sales, or Marketing) — only the label changed.

Renaming never touches the underlying data. It’s purely a schema change, so it’s just as fast on a huge table as it is on a tiny one, and any query written after the rename simply refers to the new name.

Example 3: A realistic multi-step ALTER TABLE workflow

CREATE TABLE products_catalog (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price REAL
);

INSERT INTO products_catalog (product_id, product_name, price) VALUES
  (1, 'Widget', 9.99),
  (2, 'Gadget', 19.99);

ALTER TABLE products_catalog ADD COLUMN in_stock INTEGER NOT NULL DEFAULT 1;
ALTER TABLE products_catalog ADD COLUMN category TEXT;
ALTER TABLE products_catalog RENAME COLUMN product_name TO name;
ALTER TABLE products_catalog DROP COLUMN category;
ALTER TABLE products_catalog RENAME TO catalog;

SELECT * FROM catalog;

Result:

product_id name price in_stock
1 Widget 9.99 1
2 Gadget 19.99 1

This example chains five separate ALTER TABLE actions: it adds a NOT NULL column backed by a DEFAULT (so both existing rows immediately satisfy the constraint), adds and then drops an unused category column, renames product_name to name, and finally renames the whole table. The final SELECT * confirms that only the surviving columns — product_id, name, price, and in_stock — remain, under the new table name.

How It Works Step by Step

ALTER TABLE is a Data Definition Language (DDL) statement, so it doesn’t flow through the FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY pipeline that a query uses. Instead, the engine processes it roughly like this:

  • Parse and validate — the engine checks that the target table exists and that the requested change is legal (the new column name isn’t already taken; the column being dropped or renamed does exist).
  • Classify the change — the engine decides whether the operation can be a pure metadata update (adding a column, dropping an unreferenced column, renaming a column or table) or whether it’s disallowed altogether (changing a column’s type, dropping a column used by a key/index/constraint).
  • Update the schema catalog — the new table definition is written into sqlite_master (or the equivalent system catalog in other engines), including any DEFAULT clause supplied for a new column.
  • Re-resolve dependent objects — views, triggers, indexes, and foreign keys that reference the changed table or column are updated to point at the new definition.
  • Commit — the schema change becomes visible to every subsequent statement. Because no row data had to move, this typically happens in milliseconds regardless of table size.

Every query issued after the ALTER TABLE commits — including the SELECT statements in the examples above — is then planned and executed against the new schema, going through the normal FROM → WHERE → SELECT → ORDER BY logical order as usual.

Common Mistakes

Mistake 1: Adding a NOT NULL column without a default

ALTER TABLE employees ADD COLUMN department_code TEXT NOT NULL;

This fails with an error along the lines of “Cannot add a NOT NULL column with default value NULL”. SQLite has no way to invent a value for the 4 rows that already exist in employees, so it refuses the statement rather than silently leaving a constraint violated. The fix is to supply a DEFAULT in the same statement:

ALTER TABLE employees ADD COLUMN department_code TEXT NOT NULL DEFAULT 'UNK';
SELECT id, department_code FROM employees;

Result: succeeds, and every one of the 4 existing rows now shows department_code = 'UNK', because the DEFAULT backfills the value the NOT NULL constraint requires.

Mistake 2: Trying to change a column’s data type in place

ALTER TABLE employees MODIFY COLUMN salary INTEGER;

This fails with a syntax error near MODIFY. MODIFY COLUMN is MySQL-specific syntax; SQLite’s ALTER TABLE doesn’t recognize it, and it has no equivalent ALTER COLUMN ... TYPE form either. The correct approach in SQLite is to rebuild the table with the type you actually want:

CREATE TABLE temp_scores (
  id INTEGER PRIMARY KEY,
  score TEXT
);

INSERT INTO temp_scores (id, score) VALUES (1, '95'), (2, '88');

CREATE TABLE temp_scores_new (
  id INTEGER PRIMARY KEY,
  score INTEGER
);

INSERT INTO temp_scores_new (id, score)
SELECT id, CAST(score AS INTEGER) FROM temp_scores;

DROP TABLE temp_scores;
ALTER TABLE temp_scores_new RENAME TO temp_scores;

SELECT * FROM temp_scores;

Result: 2 rows come back, (1, 95) and (2, 88), with score now stored as an INTEGER rather than TEXT — achieved entirely by rebuilding the table, never by altering the column’s type directly.

A related trap: trying to DROP COLUMN on a column used by a PRIMARY KEY, a UNIQUE/CHECK constraint, an index, or a foreign key. SQLite will reject that statement outright, so you must drop the dependent index or constraint first, or fall back to the same rebuild pattern shown above.

Best Practices

  • Back up important tables, or work inside a transaction, before running ALTER TABLE against production data.
  • Prefer adding nullable columns, or ones with a sensible DEFAULT, so existing rows remain valid the instant the column appears.
  • Don’t try to DROP COLUMN on a column referenced by a key, index, or foreign key — remove the dependent object first, or rebuild the table.
  • When you need to change a column’s data type, use the create-copy-drop-rename pattern and wrap it in a transaction so a mid-failure never leaves you with two half-finished tables.
  • Test schema changes against a staging copy of the database first, especially on engines where a particular change may still force a full table rewrite.
  • Keep ALTER TABLE statements in version-controlled migration scripts rather than running them ad hoc, so every environment ends up with an identical, reproducible schema history.

Practice Exercises

  • Given products(product_id, product_name, price, category), write an ALTER TABLE statement that adds a new, nullable column called supplier to store each product’s supplier name.
  • Rename the category column of the products table to product_category, then write a SELECT that confirms the new name works.
  • Using customers(customer_id, customer_name, country), add a NOT NULL column called signup_year with a default of 2024. Explain in your own words why SQLite requires a DEFAULT value for this statement to succeed.

Summary

  • ALTER TABLE changes an existing table’s structure — adding, dropping, or renaming columns, or renaming the table — without recreating it from scratch.
  • In SQLite, ADD COLUMN, DROP COLUMN, RENAME COLUMN, and RENAME TO are schema-metadata operations and are typically fast, even on huge tables.
  • DROP COLUMN is blocked if the column is part of a PRIMARY KEY, a UNIQUE/CHECK constraint, an index, a foreign key, or a generated column.
  • Adding a NOT NULL column requires a non-null DEFAULT, since the engine has no other value to give existing rows.
  • SQLite has no way to change a column’s data type directly — rebuild the table (create new, copy/cast the data, drop the old one, rename the new one) instead.
  • Always test ALTER TABLE statements against a copy of your data first, and wrap multi-step rebuilds in a transaction.