How to add new Column to Android SQLite Database?

Question

I have one problem with Android SQLite database.

I have one table which contains one field.StudentFname and that application is working fine with Android 2.3.1 and now if I add another field then my application is not working properly.

Can anyone help me who knows database very well,

Answer 1

I think we should not check condition like that

 if (newVersion > oldVersion) {
 }

because if we use this , it means every time when we increase the database version then onUpdrade() will call and condition will be true , so we should check like that

if(oldVersion==1) 
{

}

so in this case if old version is 2 then condition will be false and user with old version 2 will not update the database(which user wants only for version 1), because for those users database had already updated .

Answer 2

To add a new column to the table you need to use ALTER. In android you can add the new column inside the onUpgrade().

You may be wonder, how onUpgrade() will add the new column?

When you implementing a subclass of SQLiteOpenHelper, you need to call superclass constructor: super(context, DB_NAME, null, 1); in your class constructor. There I have passed 1 for version.

When I changed the version 1 to above(2 or greater), onUpgrade() will invoked. And perform the SQL modifications which I intend to do. My class constructor after changed the version:

public DatabaseHelper(Context context) {
    super(context, DB_NAME, null, 2);//version changed from 1 to 2
}

SQL modifications checks like this, superclass constructor compares the version of the stored SQLite db file with the version that I passed to super(). If these(previous and now) version numbers are different onUpgrade() gets invoked.

Code should look like this:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    // add new columns to migrate to version 2
    if (oldVersion < 2) { 
        db.execSQL("ALTER TABLE " + TABLE_NAME + "ADD COLUMN school VARCHAR(250)");
    }
    // add new columns to migrate to version 3
    if (oldVersion < 3) { 
        db.execSQL("ALTER TABLE " + TABLE_NAME + "ADD COLUMN age INTEGER");
    }
}

Answer 3

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.

Answer 4

I have done the following approach, it is resovled for me

if DB version : 6

Ex : There is a table with 5 columns

When you upgrade to : 7 ( I am adding 1 new column in the 3 tables)

 1. We need to add the columns when creating a table

 2. onUpgrade method:

 if (oldVersion < 7) 
 { 
    db.execSQL(DATABASE_ALTER_ADD_PAPER_PAID);
    db.execSQL(DATABASE_ALTER_LAST_UPLOADED);
    db.execSQL(DATABASE_ALTER_PAPER_LABEL); 
 }

Where : "DATABASE_ALTER_ADD_PAPER_PAID" is query.

EX: public static final String DATABASE_ALTER_ADD_PAPER_PAID = "ALTER TABLE "
                + TableConstants.MY_PAPERS_TABLE + " ADD COLUMN " + COLUMN_PAPER_PAID + " TEXT;";

After above two operation it will works fine for the fresh install user and app upgrade user

Answer 5

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");
    }
}

Answer 6

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.