Manage sqlite tables on SQLiteOpenHelper onUpgrade

Question

I have an android app published in Playstore. I need to add some columns to the existing table. When I drop and recreate the table, all data will be lost.

Is there any way to alter the database keeping existing data.

Answer 1

To add columns you can use ALTER TABLE your_table ADD COLUMN column_definition without dropping the tables.

Please have a read of SQL As Understood By SQLite - ALTER TABLE, as there are restrictions. The link also discusses other kinds of schema changes.

e.g. you could have something like :-

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion = 1 && newVersion > 1) {
        String alterSQL = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + NEWCOLUMN + " INTEGER DEFAULT 100";
        db.execSQL(alterSQL);
    }
}