Schemas change. A column is added, a name is corrected, an old column is retired, a type has to change. The ALTER TABLE statement makes those changes, and the syntax is not the interesting part. The interesting part is which changes each engine allows on a table that already holds data.
The previous lesson in this module covered "How Do Auto-Increment and Sequences Generate IDs?", and the next lesson in this module is about "What Do ON DELETE and ON UPDATE Actions Do?".
How does the 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 row that already exists. A default tells the engine 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 0The 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 in this case, and the refusal you get when you leave it out is worth remembering. The last demonstration in this lesson shows it.
What about renaming and dropping?
Both are straightforward. SQLite has supported them since version 3.25 for RENAME COLUMN and version 3.35 for DROP COLUMN.
A rename is a metadata change. It does not touch 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 0Renaming a table works the same way, and the change shows up 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_assignmentsDropping a column removes the column and its data in one step. 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 are worth keeping in mind.
Dropping a column is irreversible, and it destroys the data. There is no undo unless you took a backup first.
The DROP COLUMN of SQLite refuses to remove a column that is indexed, that is part of the primary key, or that a foreign key or a view refers to.
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 the type of a column?
This is where the engines diverge sharply, and the difference is worth understanding rather than memorising.
MySQL, PostgreSQL and SQL Server can change the type of a column in place, with ALTER TABLE ... ALTER COLUMN or with MODIFY.
SQLite cannot. It has no statement for the job, because a table is defined by its CREATE TABLE statement and the engine will not rewrite that behind your back.
The documented procedure is a rebuild. You create a new table with the shape you want, copy the rows across with a conversion, drop the old table and rename the new one. Each of those steps is a statement of its own, and the first one 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 and converts 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 everything that points 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 35500The four hundred and twelve pounds and fifty pence is now stored as 41250, a whole number of pence. That is the representation module 39 recommended, and it avoids floating point drift completely.
The rebuild is the price of that change in SQLite, and it shows why the first type decision matters. The same change that takes four statements here takes one in MySQL, and it costs nothing at all if the column was declared as integer minor units from day one.
What are the practical rules for the schema changes?
- You add new columns as nullable first, backfill them, and only then add the
NOT NULLconstraint, unless a default makes it safe straight away. - Changing a type on a large table means a rewrite in every engine. Plan for the lock.
- You never drop a column and hope. Rename it to something like
_retiredfirst and drop it later, once nothing refers to it. - SQLite cannot add a
UNIQUEcolumn or a primary key, and it cannot change a column type. Those need the rebuild procedure. - You test a migration against a copy of the production data, not against a handful of sample rows. The size changes the behaviour.
Quick reference
- An
ADD COLUMNfor aNOT NULLcolumn needs aDEFAULT, or SQLite refuses the change. RENAME COLUMN,RENAME TABLEandDROP COLUMNare all supported in modern SQLite and in every other engine.- Changing a column type means rebuilding the table in SQLite: create, copy, drop, rename.
- Dropping a column destroys its data for good, so rename it out of the way first.
- On a large dataset every one of these operations rewrites the table, so you schedule them.
The refusal that the engine will not bend
There is one change that SQLite simply refuses, and it is the one that catches everybody out. You cannot add a NOT NULL column to a table that already holds rows unless the column comes with a default.
The table here already holds the policies, 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 NULLThe reasoning is sound. The table already holds rows, and there is no value to give the new column in them.
There are two ways forward. You add the column as nullable and backfill it, or you 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 the channel of every policy, whether that is the web, the broker or the phone. 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.