SQLite Modify Column

Question

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 1

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

Answer 2

That's one of the better-known drawbacks of SQLite (no MODIFY COLUMN support on ALTER TABLE), but fortunately it's ranked near the top of the list of SQL features that SQLite does not implement which means it may make it into a future release.