How Does ALTER TABLE Evolve a Schema?

Schemas change. A column is added, a name is corrected, a column is retired, a type has to change. ALTER TABLE is the statement that makes those changes, and the interesting part is not the syntax but which changes each engine will let you make to a table that already holds data.

How does ALTER TABLE evolve a schema?

Adding a column is the change you will make most often, and a new column needs a value for every existing row. Supplying a default is how you say what that value is:

ALTER TABLE policies ADD COLUMN auto_renew INTEGER NOT NULL DEFAULT 0;
PRAGMA table_info(policies);

cid  name           type     notnull  dflt_value  pk
---  -------------  -------  -------  ----------  --
0    policy_id      INTEGER  0        NULL        1
1    holder_id      INTEGER  1        NULL        0
2    policy_number  TEXT     1        NULL        0
3    product        TEXT     1        NULL        0
4    premium        REAL     1        NULL        0
5    start_date     TEXT     1        NULL        0
6    end_date       TEXT     1        NULL        0
7    status         TEXT     1        'active'    0
8    auto_renew     INTEGER  1        0           0

The new column appears with a notnull flag and a default of 0, and every existing policy now has auto_renew set to zero. The default is not optional here, and the refusal the engine gives when you leave it off is worth keeping in mind — lesson 47's last demonstration shows it.

What about renaming and dropping?

Both are straightforward, and SQLite has supported them since version 3.25 for RENAME COLUMN and 3.35 for DROP COLUMN. The rename is a metadata change with no effect on the data:

ALTER TABLE policies RENAME COLUMN auto_renew TO renews_automatically;
PRAGMA table_info(policies);

cid  name                  type     notnull  dflt_value  pk
---  --------------------  -------  -------  ----------  --
0    policy_id             INTEGER  0        NULL        1
1    holder_id             INTEGER  1        NULL        0
2    policy_number         TEXT     1        NULL        0
3    product               TEXT     1        NULL        0
4    premium               REAL     1        NULL        0
5    start_date            TEXT     1        NULL        0
6    end_date              TEXT     1        NULL        0
7    status                TEXT     1        'active'    0
8    renews_automatically  INTEGER  1        0           0

Renaming a table works the same way, and the effect is visible in the schema listing:

ALTER TABLE policies RENAME TO policy_records;
SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'policy%';

name
policyholders
policy_records
policy_assignments

Dropping a column removes it and its data in one step — and the statement has to name the table as it stands now, not as it stood before the rename:

ALTER TABLE policy_records DROP COLUMN renews_automatically;

Three things to keep in mind. Dropping a column is irreversible and destroys data — there is no undo unless you took a backup first. SQLite's DROP COLUMN refuses to remove a column that is indexed, part of a primary key, or referenced by a foreign key or a view. And on a large table these operations rewrite every row, so they belong in a maintenance window rather than in the middle of the trading day.

How do you change a column's type?

This is where the engines diverge sharply, and the divergence is worth understanding rather than memorising. MySQL, PostgreSQL and SQL Server can change a column's type in place with ALTER TABLE ... ALTER COLUMN or MODIFY. SQLite cannot — it has no statement for it at all, because its tables are defined by a CREATE TABLE statement that the engine does not want to rewrite behind your back. The documented procedure is a rebuild: create a new table with the desired shape, copy the rows across with a conversion, drop the old one, rename the new one.

Every one of those steps is a statement of its own, and the first of them declares the shape the new table should have:

CREATE TABLE policies_new (
  policy_id     INTEGER PRIMARY KEY,
  holder_id     INTEGER NOT NULL REFERENCES policyholders(holder_id),
  policy_number TEXT    NOT NULL UNIQUE,
  product       TEXT    NOT NULL,
  premium_cents INTEGER NOT NULL,
  start_date    TEXT    NOT NULL,
  end_date      TEXT    NOT NULL,
  status        TEXT    NOT NULL DEFAULT 'active'
);

The second step copies the rows across, converting the premium from pounds to pence as it goes:

INSERT INTO policies_new (policy_id, holder_id, policy_number, product, premium_cents, start_date, end_date, status)
SELECT policy_id, holder_id, policy_number, product, CAST(ROUND(premium * 100) AS INTEGER), start_date, end_date, status
FROM policy_records;

The last two steps swap the tables over, so that everything pointing at the name keeps pointing at the right rows:

DROP TABLE policy_records;
ALTER TABLE policies_new RENAME TO policy_records;
SELECT policy_id, policy_number, premium_cents FROM policy_records ORDER BY policy_id LIMIT 4;

policy_id  policy_number  premium_cents
---------  -------------  -------------
1          POL-2020-0001  41250
2          POL-2020-0002  9600
3          POL-2021-0003  8875
4          POL-2021-0004  35500

Four hundred and twelve pounds and fifty pence is now stored as 41250 — an integer number of pence, which is the representation module 39 recommended and the one that avoids floating point drift entirely. The rebuild is the price of that change in SQLite, and it is a good illustration of why the initial type decision matters: the same change that costs four statements here costs one in MySQL, and costs nothing at all if the column was declared as integer minor units on day one.

What are the practical rules for schema changes?

• Add columns as nullable first, backfill, then add the NOT NULL constraint — unless a default makes it safe immediately.
• Changing a type on a large table means a rewrite everywhere, in every engine. Plan for the lock.
• Never drop a column and hope; rename it to something like _retired first, and drop it later once nothing references it.
• SQLite cannot add a UNIQUE column, a primary key, or change a column type — those need the rebuild procedure.
• Test the migration against a copy of production data, not a handful of sample rows. Size changes behaviour.

Quick reference

• ADD COLUMN with a NOT NULL column requires a DEFAULT, or SQLite refuses the change.
• RENAME COLUMN, RENAME TABLE and DROP COLUMN are all supported in modern SQLite and every other engine.
• Changing a column type means rebuilding the table in SQLite: create, copy, drop, rename.
• Dropping a column destroys its data irreversibly — rename it out of the way first.
• Every one of these operations rewrites the table on a large dataset, so schedule them.

The refusal the engine will not bend

One change SQLite simply refuses, and it is the one that catches everybody out: a NOT NULL column cannot be added to a table that already holds rows unless it comes with a default. The table here has policies in it, so the change is rejected outright:

ALTER TABLE policy_records ADD COLUMN lapse_reason TEXT NOT NULL;

Runtime error near line 43: Cannot add a NOT NULL column with default value NULL

The reasoning is sound: the table already holds rows, and there is no value to give the new column in them. The two ways forward are to add the column as nullable and backfill it, or to add it with a default and then remove the default — which is what the first statement in this lesson effectively did.

Migration drill: the insurer wants to record a policy's channel — web, broker or phone — on every existing policy. Write the sequence of statements that adds the column safely and backfills it, then say what SQLite requires you to do differently when you come to enforce the NOT NULL constraint on the column afterwards.