For as long as I've used SQLite, adding a NOT NULL constraint to an existing column meant the twelve-step dance: create a new table with the constraint you want, copy every row across, drop the old table, rename the new one, and rebuild whatever indexes you just lost in the process. SQLite 3.53.0, released in April 2026, adds ALTER TABLE ... ALTER COLUMN ... SET NOT NULL and DROP NOT NULL, so I built a few test tables and measured what actually changes.
I ran this against the real 3.53.4 CLI, not the one that ships in Ubuntu's apt repositories, which still tops out at 3.45.1 and doesn't understand the new syntax at all.
The rebuild, timed
I generated a 2 million row table with an index on the column I was about to constrain, dropped the OS page cache before each run (echo 3 > /proc/sys/vm/drop_caches) so I wasn't measuring the filesystem cache instead of SQLite, and timed both approaches three times each.
The old way:
CREATE TABLE t_new(id INTEGER PRIMARY KEY, email TEXT NOT NULL, created_at TEXT);
INSERT INTO t_new SELECT * FROM t;
DROP TABLE t;
ALTER TABLE t_new RENAME TO t;
CREATE INDEX idx_email ON t(email);
The new way:
ALTER TABLE t ALTER COLUMN email SET NOT NULL;
| Approach | Run 1 | Run 2 | Run 3 |
|---|---|---|---|
| Rebuild (old way) | 1.788s | 1.495s | 1.594s |
SET NOT NULL (indexed column) |
0.0093s | 0.0113s | 0.0097s |
That's roughly 150 to 180 times faster, and it isn't a rounding trick: the rebuild also has to recreate idx_email from scratch, which the old approach needs you to remember to do by hand. Forget that step and your queries silently fall back to a table scan. SET NOT NULL leaves the index exactly where it was.
Why it's that fast: it barely touches the disk
The speed difference made me suspicious, so I ran both under strace -c counting pwrite64 calls on a fresh copy of the same database.
=== OLD WAY ===
% time seconds usecs/call calls errors syscall
100.00 1.001682 14 69843 pwrite64
=== NEW WAY ===
% time seconds usecs/call calls errors syscall
0.00 0.000000 0 6 pwrite64
Six writes totalling 8,716 bytes, against 69,843 writes for the rebuild. The rebuild touches every row twice — once to write it into the new table, once again when the index is rebuilt. SET NOT NULL only rewrites the schema record in sqlite_master; the row data pages never move.
The catch: it still has to read every row, unless there's an index
Here's the finding that argues against the headline number. SQLite's own release notes say ALTER TABLE execution time for a new NOT NULL constraint "is proportional to the amount of data in the table," because every existing row has to be checked. On my first table that wasn't visible at all — 0.01 seconds for 2 million rows looked too good to be true, so I ran it again on a column with no index.
=== indexed column, 8M rows: read syscalls ===
calls
13 pread64
=== unindexed column, 8M rows: read syscalls ===
calls
25372 pread64
With an index on the target column, SQLite checks for nulls by walking the index itself — NULLs sort first in SQLite's b-trees, so it only has to look at the left edge, 13 page reads regardless of table size. Without an index, it has to scan every data page: 25,372 reads for an 8 million row table.
On a warm cache this difference nearly disappears in wall-clock terms (both finish in well under a second, because Linux is just serving pages out of RAM). Cold-cache numbers tell a clearer story:
| Column has index? | Cold-cache time (8M rows, created_at / email) |
|---|---|
| Yes | ~0.010s |
| No | ~0.11s |
Still much faster than a rebuild either way, but the documentation's "proportional to table size" claim only really bites when there's no index backing the column you're constraining.
What it refuses, and how unhelpfully
Feed it a table with an existing null and it refuses, correctly:
$ sqlite3 t.db "ALTER TABLE t ALTER COLUMN b SET NOT NULL;"
Error in 2nd command line argument: constraint failed
Compare that with the error you get for the exact same violation through a normal INSERT:
$ sqlite3 t.db "INSERT INTO t VALUES (1, NULL);"
Error near line 1: NOT NULL constraint failed: t.b
The INSERT error names the table and the column. The ALTER TABLE error says "constraint failed" and nothing else — not which column, not which constraint type, not how many rows fail or which row id. On a real table with several nullable columns you're trying to lock down one at a time, that message won't tell you which one is the problem. You have to go find the offending rows yourself, with something like SELECT rowid FROM t WHERE email IS NULL LIMIT 5.
A working feature nowhere in the docs
While testing CHECK constraints I tried the ANSI-SQL-style syntax out of habit, expecting a parse error, because SQLite's own lang_altertable.html page only documents SET NOT NULL / DROP NOT NULL and says CHECK constraints can only be added via ADD COLUMN.
ALTER TABLE t ADD CONSTRAINT age_positive CHECK (age >= 0);
It worked. It validated existing rows, it rejected a negative insert afterwards with a properly named error (CHECK constraint failed: age_positive), and ALTER TABLE t DROP CONSTRAINT age_positive; cleanly removed it again. I checked the raw HTML of the ALTER TABLE reference page for the literal strings "ADD CONSTRAINT" and "DROP CONSTRAINT" — zero matches, in either direction. This is a fully working, round-trippable feature that I couldn't find documented anywhere on sqlite.org.
Locking: reads pass through, writes don't
I used a FIFO to keep one sqlite3 CLI session open with an uncommitted BEGIN IMMEDIATE write transaction, then tried to run the ALTER from a second connection against the locked database.
--- ALTER with busy_timeout=0, while a write lock is held ---
Error in 2nd command line argument: database is locked
immediate-fail attempt wall=0.004s
--- ALTER with busy_timeout=3000, same lock held for 0.3s then released ---
wait-then-succeed attempt wall=0.982s
With no timeout it fails immediately. With a timeout it queues and runs as soon as the lock is free, same as any other write. Unsurprising, but worth confirming, since it means you do need busy_timeout set on whatever runs this migration, same as any other schema change.
The more interesting case is a concurrent reader rather than a concurrent writer. I started the slow (unindexed, 8M row) SET NOT NULL in the background, let it get 150ms into its scan, then fired a SELECT count(*) FROM t from a second connection with busy_timeout=0, in both the default rollback-journal mode and WAL mode:
DELETE mode: reader wall=0.097s, returned 8000000, ALTER total wall=0.658s
WAL mode: reader wall=0.111s, returned 8000000, ALTER total wall=0.322s
The reader succeeded immediately in both modes, without waiting for the ALTER to finish. The NOT NULL scan only needs a shared lock for the read phase; it doesn't take an exclusive lock until the very end, for that tiny schema write. If your application is read-heavy, running this migration won't stall your readers even on a large table.
The no-op claim checks out
The docs say calling SET NOT NULL on a column that's already NOT NULL is a no-op. I ran it twice in a row:
ALTER TABLE t ALTER COLUMN email SET NOT NULL; -- succeeds, adds the constraint
ALTER TABLE t ALTER COLUMN email SET NOT NULL; -- succeeds again, exit code 0
The schema was identical before and after the second call, and the second call didn't trigger the pread64-heavy validation scan a fresh constraint would. DROP NOT NULL behaved correctly too: after dropping it, an INSERT with a null value in that column succeeded again.
What I got wrong on the way
My first attempt at the concurrency test used a background shell loop that checked for the existence of a flag file before writing anything. The loop and the touch of that flag file were two separate commands, and when I ran them back to back, the loop occasionally started, checked for the file, found it missing, and exited — before the touch had actually run. The result was a test that reported "0 writer attempts logged" and looked, misleadingly, like no concurrent activity had happened at all. I rebuilt it using a named pipe holding one persistent sqlite3 session with an explicit BEGIN IMMEDIATE, which gave me a lock I controlled directly instead of a lock I was hoping a loop would acquire in time.
Run it yourself
Get the real CLI, since distro packages lag badly behind:
curl -sS -L -o sqlite-tools.zip \
https://www.sqlite.org/2026/sqlite-tools-linux-x64-3530400.zip
unzip -o -q sqlite-tools.zip
./sqlite3 --version # should print 3.53.4 or later
Build a test table and compare the two approaches:
./sqlite3 bench.db <<'EOF'
CREATE TABLE t(id INTEGER PRIMARY KEY, email TEXT, created_at TEXT);
WITH RECURSIVE seq(x) AS (
SELECT 1 UNION ALL SELECT x+1 FROM seq WHERE x < 2000000
)
INSERT INTO t(id, email, created_at)
SELECT x, 'user'||x||'@example.com', datetime('now') FROM seq;
CREATE INDEX idx_email ON t(email);
EOF
cp bench.db newway.db
time ./sqlite3 newway.db "ALTER TABLE t ALTER COLUMN email SET NOT NULL;"
cp bench.db oldway.db
time ./sqlite3 oldway.db "
CREATE TABLE t_new(id INTEGER PRIMARY KEY, email TEXT NOT NULL, created_at TEXT);
INSERT INTO t_new SELECT * FROM t;
DROP TABLE t;
ALTER TABLE t_new RENAME TO t;
CREATE INDEX idx_email ON t(email);
"
If you want the write-count comparison, run the same two commands under strace -f -e trace=pwrite64 -c instead of time.
What to do with this
If you're on SQLite 3.53 or later and need to tighten a schema that's already in production, use ALTER TABLE ... ALTER COLUMN ... SET NOT NULL without hesitation — it's faster, it doesn't disturb your indexes, and it won't block concurrent readers. But check your target column for nulls yourself first, with an explicit SELECT ... WHERE col IS NULL, before you run it on anything you can't immediately re-run: the error message won't tell you where the violation is. And if you need a CHECK constraint added or removed on a live table, ADD CONSTRAINT and DROP CONSTRAINT already work, even though the manual doesn't mention them yet — just don't be surprised if that's a documentation gap rather than a permanent feature, and keep an eye on future release notes in case the syntax changes before it's formally written up.
Top comments (0)