In Android SQLite shall we add the new column in onCreate() too?

Question

I want to add new column for one of my tables in SQLite.

I know I can achieve this for existing users by adding below in OnUpgrade() method

db.execSQL("ALTER TABLE my_table ADD COLUMN my_column2 INTEGER DEFAULT 0");

and In onCreate() method the table creation will remain as below

 db.execSQL("CREATE TABLE IF NOT EXISTS \"my_table\" (\"coulmn_1\" INTEGER PRIMARY KEY  NOT NULL  UNIQUE)");

Now what if a new user install the application? only onCreate() will be called because user doesn't have any previous version of the database. And in onCreate() method it doesn't include the new column coulmn_2

So shall I update the table creation in onCreate() too? or it will cause issues for existing users?

Answer 1

Now what if a new user install the application? only onCreate() will be called because user doesn't have any previous version of the database.

This is why the CREATE TABLE statement inside onCreate() must include the new column:

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(
        "CREATE TABLE IF NOT EXISTS my_table(" +
        "column_1 INTEGER PRIMARY KEY  NOT NULL, " +
        "my_column2 INTEGER DEFAULT 0)"
    );
}

The method onCreate() is called only if the database does not exist, meaning that the above statement will be executed on devices that do not have your previous version of the app with the old database.

For a user who has already installed the previous version of your app onCreate() will not be called, but onUpgrade() will be executed and there you should place the code that adds the new column.
So, change the version of your database for example to 2 (this is not the same as the version of your app) and:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 2) {
         db.execSQL("ALTER TABLE my_table ADD COLUMN my_column2 INTEGER DEFAULT 0");
    }
}