55

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
  • 5,059
  • 16
  • 48
  • 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

52

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
  • 55,313
  • 14
  • 116
  • 115
32

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,327
  • 17
  • 25
  • 1
    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
  • @rubo77 In that case, it's probably better to do everything inside the database: rename the table to something else, create the table new how you want it, copy all the data from the renamed table to the new one, then drop the old table. – lmat - Reinstate Monica Nov 17 '22 at 18:00
13

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,663
  • 14
  • 81
  • 103
  • 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
  • 595
  • 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,392
  • 11
  • 18