Default values in SQLite3

Question

When I created the table structure of my SQLite database, I provided a DEFAULT value which is used when no value is provided by the user. Now, because the application code changed, I need to modify the default but can't seem to find how to do it.

Is it possible?

Answer 1

SQLite only implements a very limited subset of ALTER TABLE statements. You'll have to drop and recreate the table, preferably in one transaction:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE tbl_tmp(col1, col2);
INSERT INTO tbl_tmp SELECT col1, col2 FROM tbl;
DROP TABLE tbl;
CREATE TABLE tbl(col1 integer, col2 text DEFAULT 'foo'); -- with new default
INSERT INTO tbl SELECT col1, col2 FROM tbl_tmp;
DROP TABLE tbl_tmp;
COMMIT;