How to add new Column to Android SQLite Database?

Solution 1:

you can use ALTER TABLE function on your onUpgrade() method, like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // If you need to add a column
  if (newVersion > oldVersion) {
     db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
  }
}

Obviously, the SQLite will differ depending on the column definition.

Solution 2:

I came across this thread when needing help on my own app, but saw issues with many of the answers. I would recommend doing the following:

private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL(DATABASE_ALTER_TEAM_1);
    }
    if (oldVersion < 3) {
         db.execSQL(DATABASE_ALTER_TEAM_2);
    }
}

You want to make sure the code will work when users upgrade more than 1 version and that the update statement only runs the one upgrade it is needed. For a bit more on this, check out this blog.

Solution 3:

The easiest way to do this is to add some SQL to the onUpgrade() method in your SQLiteOpenHelper class. Something like:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // If you need to add a new column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE student ADD COLUMN student_rollno INTEGER DEFAULT 0");
    }
}