SQLite add column, keep data

Please see this page for the syntax to create a new column on a table. Basically it is:

ALTER TABLE mytable ADD COLUMN mycolumn TEXT

In your onUpgrade method, it would look something like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
    if (oldVersion == 1 && newVersion == 2)
         db.execSQL(upgradeQuery);
}

I have not worked with android, but sqlite provides 'alter table' as most SQL implementations does:

SQLite alter table


A better approach

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch (oldVersion) {
        case 1:
            db.execSQL(SQL_MY_TABLE);


        case 2:
            db.execSQL("ALTER TABLE myTable ADD COLUMN myNewColumn TEXT");

    }
}

Lets say in case 1, you upgraded to db version 2. You created a new table but forgot the myNewColumn you will see in case 2. What this will do is if you change the db version to 3, case 2 will get ran if its upgrading from 2 to 3.

Tags:

Sqlite

Android