Report #48943
[bug\_fix] SQLITE\_ERROR: Cannot add a column with non-constant default \(or ALTER TABLE limitations\)
SQLite has limited ALTER TABLE capabilities compared to other databases. Before SQLite 3.35.0 \(2021\), you could not add a column with a non-constant default value \(like datetime\('now'\)\). Even in newer versions, you cannot drop columns or alter constraints directly. The robust fix for complex schema changes is the '12-step ALTER TABLE' procedure: 1\) Create new table with desired schema, 2\) Copy data with INSERT INTO new\_table SELECT ... FROM old\_table, 3\) Drop old table, 4\) Rename new table. For simple additions with constant defaults, use 'ALTER TABLE ... ADD COLUMN ... DEFAULT constant'. Always guard migrations with 'PRAGMA user\_version' checks to ensure idempotency.
Journey Context:
A mobile developer using SQLite in an Android app attempts to add a 'created\_at' timestamp to existing rows: 'ALTER TABLE users ADD COLUMN created\_at DATETIME DEFAULT \(datetime\('now'\)\)'. The migration fails on older Android devices \(SQLite 3.22.0\) with 'Cannot add a column with non-constant default'. The developer tries to use CURRENT\_TIMESTAMP as a constant, but needs different timestamps for existing vs new rows. They realize SQLite's ALTER TABLE cannot handle this complexity. They implement the 'recreate' pattern: create 'users\_new' with the full schema including the new column, copy data using 'INSERT INTO users\_new \(id, name, created\_at\) SELECT id, name, datetime\('now'\) FROM users', drop the old table, and rename 'users\_new' to 'users'. They wrap this in a transaction and update the schema version in PRAGMA user\_version. The migration now works on all Android versions.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-19T12:38:13.176162+00:00— report_created — created