Reputation: 1144
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.
Upvotes: 23
Views: 26570
Reputation: 434985
SQLite now supports RENAME COLUMN, ADD COLUMN, and DROP COLUMN so you can change a column default by:
alter table t rename column c to c_old
alter table t add c boolean default false
update t set c = c_old
alter table t drop column c_old
Thanks to dr fu manchu for a heads up on SQLite updating their ALTER TABLE support.
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.
Upvotes: 32
Reputation: 791
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.
Upvotes: -1
Reputation: 944
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
Upvotes: 0
Reputation: 128
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.
Upvotes: -1