How to add new Column to Android SQLite Database?

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,

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.

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.

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

Perhaps a slightly more elegant approach using switch instead of if/then that will upgrade from any schema to the most recent schema...

Here's also a decent page on the syntax for altering a table: http://alvinalexander.com/android/sqlite-alter-table-syntax-examples

public static final String TABLE_TEAM = "team";
public static final String COLUMN_COACH = "coach";
public static final String COLUMN_STADIUM = "stadium";


private static final String DATABASE_ALTER_TEAM_TO_V2 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " TEXT;";

private static final String DATABASE_ALTER_TEAM_TO_V3 = "ALTER TABLE "
    + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " TEXT;";


@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    
    switch (oldVersion)
    {
        case 1:
            //upgrade from version 1 to 2
            db.execSQL(DATABASE_ALTER_TEAM_TO_V2);
        case 2:
            //upgrade from version 2 to 3
            db.execSQL(DATABASE_ALTER_TEAM_TO_V3);

        //and so on.. do not add breaks so that switch will
        //start at oldVersion, and run straight through to the latest

    }

}

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

@Aamirkhan.i think you would have solved the problem you mentioned in the comments long back ago.i think you didn't increase the data base version. or else the answers here are straight forward.i am writing this because it might help anyone who hasn't increased or changed their data base version number when they are altering a table.

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 .

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

Simply change in your code

> private final int DATABASE_VERSION = 1;

to

> private static final int DATABASE_VERSION =2;

I came across link while searching for the same issue. It explains how to use upgrade. https://thebhwgroup.com/blog/how-android-sqlite-onupgrade

it explains why to use this below code

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

The easiest way to create a new column on a table is add some SQL to the onUpgrade() method in SQLiteOpenHelper class. Like:

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

first, increment the database version number. and then in onUpgrade method, you can check if the column exists already if not then only add the column

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   if(!existsColumnInTable(db, TABLE_NAME, COLUMN_NAME)){
      db.execSQL("ALTER TABLE foo ADD COLUMN COLUMN_NAME INTEGER DEFAULT 0");
 } 
 }

private boolean existsColumnInTable(SQLiteDatabase inDatabase, String inTable, String columnToCheck) {
    Cursor cursor = null;
    try {
        // Query 1 row
        cursor = inDatabase.rawQuery("SELECT * FROM " + inTable + " LIMIT 0", null);

        // getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
        if (cursor.getColumnIndex(columnToCheck) != -1)
            return true;
        else
            return false;

    } catch (Exception Exp) {
        return false;
    } finally {
        if (cursor != null) cursor.close();
    }
}

this method is extracted from this link https://stackoverflow.com/questions/4719594/checking-if-a-column-exists-in-an-application-database-in-android

Noticed no answers look for the existence of a column:

Cursor cursor = database.rawQuery("SELECT * FROM " + MY_TABLE, null);
int columnIndex = cursor.getColumnIndex(MY_NEW_COLUMN);
if (columnIndex < 0) {
   database.execSQL("ALTER TABLE " + MY_TABLE + " ADD COLUMN " + MY_NEW_COLUMN + " TEXT");
}