Cannot add a NOT NULL column with default value NULL

What it means

SQLite adds a column without rewriting the table: existing rows read the new column’s default value. That only works if the default is a fixed value every row can share, so ALTER TABLE … ADD COLUMN has rules. A NOT NULL column with no default would leave every existing row with NULL, so SQLite refuses it with SQLITE_ERROR (code 1) and this message. The table is unchanged.

In our tests (SQLite 3.51.0), the same statement worked on an empty table: the check only fails when there are rows. The other rules, each with its own message:

Common causes

1. A required column added to a table that has rows: a migration or hand-written ALTER TABLE customers ADD COLUMN country TEXT NOT NULL.
2. A timestamp default: created_at TEXT DEFAULT CURRENT_TIMESTAMP on a new column, which is fine in CREATE TABLE but not in ADD COLUMN.
3. A unique or key column: adding a UNIQUE code or a new PRIMARY KEY.
4. A foreign key with a default while PRAGMA foreign_keys is on. Some tools turn it on for every connection, so a statement that worked in one tool fails in another.

How to fix it

Give the column a constant default

ALTER TABLE customers ADD COLUMN country TEXT NOT NULL DEFAULT 'GB';

Every existing row reads GB; update the rows that need something else afterwards. Pick a default that’s true or obviously a placeholder ('', 0, 'unknown'), not one that looks like real data.

Add it nullable, then fill it

When there’s no sensible default, or the value has to be computed:

ALTER TABLE customers ADD COLUMN updated_at TEXT;
UPDATE customers SET updated_at = CURRENT_TIMESTAMP;

The column stays nullable, so new rows need the value from your code (or a trigger). Making it NOT NULL or giving it a CURRENT_TIMESTAMP default later takes a table rebuild.

Add a unique index instead of a UNIQUE column

ALTER TABLE customers ADD COLUMN code TEXT;
CREATE UNIQUE INDEX customers_code ON customers (code);

The index enforces uniqueness the same way a UNIQUE constraint does. The new column starts as NULL in every row, and NULLs don’t clash, so the index builds; fill the codes afterwards.

Rebuild the table for anything else

For a new primary key, a NOT NULL column without a fixed default, or a foreign key with a default, create the table again with the column you want and copy the rows. SQLite’s documentation gives the 12 steps; in short, with foreign keys off and inside a transaction:

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE new_customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  country TEXT NOT NULL
);
INSERT INTO new_customers (id, name, country)
  SELECT id, name, 'GB' FROM customers;
DROP TABLE customers;
ALTER TABLE new_customers RENAME TO customers;
-- recreate the table's indexes, triggers and views here
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

Replace 'GB' with whatever computes each row’s value. Django’s migrations and Alembic’s batch mode can do this rebuild for you.

Reproduce it

macOS /usr/bin/sqlite3, SQLite 3.51.0, with two rows in customers (id, name):

sqlite3 shop.db "ALTER TABLE customers ADD COLUMN country TEXT NOT NULL;"

Error: stepping, Cannot add a NOT NULL column with default value NULL

The shell adds “Error: stepping,”; SQLite’s message is Cannot add a NOT NULL column with default value NULL. The other rules:

Error: stepping, Cannot add a column with non-constant default
Error: in prepare, Cannot add a UNIQUE column
Error: in prepare, Cannot add a PRIMARY KEY column
Error: stepping, cannot add a STORED column
Error: stepping, Cannot add a REFERENCES column with non-NULL default value

The REFERENCES one appeared only after PRAGMA foreign_keys = ON; with foreign keys off, the same column was added. On an empty table, ADD COLUMN c TEXT NOT NULL and ADD COLUMN c TEXT DEFAULT CURRENT_TIMESTAMP both succeeded.

With DEFAULT 'GB', the column was added and both rows read GB. The nullable updated_at followed by the UPDATE filled both rows, the rebuild above produced a country column holding GB in both rows, and the code column with a unique index rejected a duplicate code afterwards with UNIQUE constraint failed: customers.code.

In Inlet

Every SQLite connection in Inlet turns foreign keys on, so adding a REFERENCES column with a non-NULL default fails there even if it worked in a tool that leaves them off. The structure editor shows the DDL for a change before it runs, so you can see the ALTER TABLE and its default first.