sqlite3 change column default value

Question

How do I change the default value of an existing column in a table in sqlite3?

I have a table named notes with a boolean column named hidden. The default is set to true, I want to set it to false.

score 28 · Accepted Answer · answered Mar 30 '12 at 01:23

I don't think you can without replacing the whole table. From the fine manual:

SQL Features That SQLite Does Not Implement

Complete ALTER TABLE support
Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.

So there is no way to modify an existing column in SQLite. I think you'll have to create a new table with the appropriate default for hidden, copy all the data over, drop the original notes table, and then rename the new one.

SQLite stays lean by purposefully omitting a lot of features.

One point might help in this regard. Before running `drop table ` make sure you run `.dump` and get the schema dump and data as well. You can then use it to create the modified schema quickly and ingest the data. — codarrior, Aug 23 '17 at 02:37

score 0 · Answer 2 · answered Dec 06 '13 at 19:11

0

SQLite database browser allows you to drop columns, so you could drop the column with it, and then manually add the column with the default using the sqlite3 command line tool.

answered Dec 06 '13 at 19:11

JeffK

131
1
7

Unfortunately to accomplish this it probably performs all kind of shenanigans under the hood like creating a temp table :| http://stackoverflow.com/q/805363/32453 – rogerdpack Oct 20 '16 at 22:44

score 0 · Answer 3 · answered Jun 25 '20 at 03:46

Create a new database with the same schema (but your new default value), attach both, and migrate.

For deleting columns or other changes that aren't supported by the "ALTER TABLE" syntax, I create a new table, migrate date into it, drop the old table, and rename the new table to the original name.

https://stackoverflow.com/a/998652/1020467

https://www.sqlite.org/lang_altertable.html

score 0 · Answer 4 · answered Aug 08 '23 at 16:30

A viable way to handle modifiable default values is to store the default values in another table. Read from that table prior to performing an insert operation to get the current default. This way the default value is just plain-ole-data in the database instead of part of the schema.

sqlite3 change column default value

4 Answers4