CODEWITHSUNDEEP
sqlite
kentor
kentor

Reputation: 1144

sqlite3 change column default value

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

Answers (4)

mu is too short
mu is too short

Reputation: 434985

New Versions

SQLite now supports RENAME COLUMN, ADD COLUMN, and DROP COLUMN so you can change a column default by:

  1. Renaming the column: alter table t rename column c to c_old
  2. Adding the column with the new default value: alter table t add c boolean default false
  3. Copying the data: update t set c = c_old
  4. Removing the old column: alter table t drop column c_old

Thanks to dr fu manchu for a heads up on SQLite updating their ALTER TABLE support.

Older Versions

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

Russell Trahan
Russell Trahan

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

mcint
mcint

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

JeffK
JeffK

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

Related Questions