53

I need to modify a column in a SQLite database but I have to do it programatically due to the database already being in production. From my research I have found that in order to do this I must do the following.

  • Create a new table with new schema
  • Copy data from old table to new table
  • Drop old table
  • Rename new table to old tables name

That seems like a ridiculous amount of work for something that should be relatively easy. Is there not an easier way? All I need to do is change a constraint on a existing column and give it a default value.

Nathan
  • 4,809
  • 16
  • 46
  • 61
  • Possible duplicate of [Modify a Column's Type in sqlite3](http://stackoverflow.com/questions/2083543/modify-a-columns-type-in-sqlite3) – rubo77 Jan 10 '17 at 20:01
  • I created a script to rename a field in a table here. http://stackoverflow.com/a/41577393/1069083 – rubo77 Jan 10 '17 at 20:04
  • SQLite has limited ALTER functionality now: https://www.sqlite.org/lang_altertable.html – CAD bloke Feb 14 '20 at 04:18

5 Answers5

49

That's one of the better-known drawbacks of SQLite (no MODIFY COLUMN support on ALTER TABLE), but it's on the list of SQL features that SQLite does not implement.

edit: Removed bit that mentioned it may being supported in a future release as the page was updated to indicate that is no longer the case

Daniel DiPaolo
  • 53,439
  • 13
  • 112
  • 113
29

If the modification is not too big (e.g. change the length of a varchar), you can dump the db, manually edit the database definition and import it back again:

echo '.dump' | sqlite3 test.db > test.dump

then open the file with a text editor, search for the definition you want to modify and then:

cat test.dump | sqlite3 new-test.db
Davide Vernizzi
  • 1,287
  • 18
  • 25
  • On my 3BG homeserver.db with lots of large tables, this takes 100 hours to dump the shema. isnt there a way to just dump that one table from my database, delete it inside the DB and then insert the changed table again? – rubo77 Jan 10 '17 at 19:08
10

As said here, these kind of features are not implemented by SQLite.

As a side note, you could make your two first steps with a create table with select:

CREATE TABLE tmp_table AS SELECT id, name FROM src_table
Jhonny D. Cano -Leftware-
  • 17,104
  • 14
  • 80
  • 102
  • 3
    Nice trick, this save me from asking "How can I change fields order in a table?". – SIFE Mar 03 '13 at 14:37
  • The only case the side note works (thanks to @SIFE) is a column reordering. For any column def change you still need 2 steps: CREATE, INSERT. ref: https://www.sqlite.org/lang_createtable.html – epox Nov 19 '17 at 22:19
1

When I ran "CREATE TABLE tmp_table AS SELECT id, name FROM src_table", I lost all the column type formatting (e.g., time field turned into a integer field

As initially stated seems like it should be easier, but here is what I did to fix. I had this problem b/c I wanted to change the Not Null field in a column and Sqlite doesnt really help there.

Using the 'SQLite Manager' Firefox addon browser (use what you like). I created the new table by copying the old create statement, made my modification, and executed it. Then to get the data copied over, I just highlighted the rows, R-click 'Copy Row(s) as SQL', replaced "someTable" with my table name, and executed the SQL.

rich
  • 575
  • 1
  • 7
  • 15
0

Various good answers already given to this question, but I also suggest taking a look at the sqlite.org page on ALTER TABLE which covers this issue in some detail: What (few) changes are possible to columns (RENAME|ADD|DROP) but also detailed workarounds for other operations in the section Making Other Kinds Of Table Schema Changes and background info in Why ALTER TABLE is such a problem for SQLite. In particular the workarounds point out some pitfalls when working with more complex tables and explain how to make changes safely.

Andrew Richards
  • 1,062
  • 8
  • 15