SQLite Modify Column

sqlite alter-table
Nathan picture Nathan · Apr 21, 2010 · Viewed 75.3k times · Source

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.

Answer

Daniel DiPaolo picture Daniel DiPaolo · Apr 21, 2010

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

Related questions

How do I rename a column in a SQLite database table?

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was …

Read More
ALTER TABLE ADD COLUMN IF NOT EXISTS in SQLite

We've recently had the need to add columns to a few of our existing SQLite database tables. This can be done with ALTER TABLE ADD COLUMN. Of course, if the table has already been altered, we want to leave it …

Read More
Modify a Column's Type in sqlite3

I'm pretty new to SQLite 3 and just now I had to add a column to an existing table I had. I went about doing that by doing: ALTER TABLE thetable ADD COLUMN category;. Of course, I forgot to specify that …

Read More