How can I add a new column to an Android SQLite Database

I wanted to add a new column to an Android SQLite Database and then I recieved errors开发者_JS百科. What should I do and how? I thought that I have to delete the table and create a new one but i don't know how.

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 column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
    }
}

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

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

The right way to add new column to DB, for example in version 2, would be:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL("ALTER TABLE table_name ADD COLUMN new_column_name TEXT");
    }
}

It covers all pitfalls, including major issue: if a user goes from version 1 to 3 they will miss the upgrade query completely! These users will be in an awkward limbo where they are missing a few of the intermediate updates and do not have the expected sql schema.

Also don't forget to alter the create statement adding new column.