Duplicate Column Name lock_status

Question

I have added lock_status column in database and in onUpgrade() Alter Table,but When I try to open my App, it force stops with the following error. What should I do in onUpgrade(), I have updated my database 4th time with new column. Help is highly Appreciated.

 Caused by: android.database.sqlite.SQLiteException: duplicate column name: lock_status (code 1): , while compiling: ALTER TABLE notes ADD COLUMN lock_status INT
  at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
  at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:887)
  at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:498)
  at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
  at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
  at android.database.sqlite.SQLiteStatement.<init>(SQLiteStatement.java:31)
  at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1674)
  at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
  at com.revodroid.notes.notes.Adapter.DatabaseHelper.onUpgrade(DatabaseHelper.java:89)
  at android.database.sqlite.SQLiteOpenHelper.getDatabaseLocked(SQLiteOpenHelper.java:256)
  at android.database.sqlite.SQLiteOpenHelper.getReadableDatabase(SQLiteOpenHelper.java:187)
  at com.revodroid.notes.notes.Adapter.DatabaseHelper.getAllNotes(DatabaseHelper.java:170)
  at com.revodroid.notes.notes.Activity.MainActivity.setupNotesAdapter(MainActivity.java:603)
  at com.revodroid.notes.notes.Activity.MainActivity.onCreate(MainActivity.java:256)
  at android.app.Activity.performCreate(Activity.java:6251)

My Database Code:

public class DatabaseHelper extends SQLiteOpenHelper {

private static final String LOG=DatabaseHelper.class.getSimpleName();
private static final int DATABASE_VERSION=4;
public static final String DATABASE_NAME="NotesDB";
public SQLiteDatabase database;

private final Context context;

DatabaseHelper databaseHelper;

public static final String TABLE_NOTES="notes";
public static final String TABLE_PASSCODE = "passcodetable";

public static final String KEY_ID="id";
public static final String KEY_TITLE="title";
public static final String KEY_CONTENT="content";
public static final String KEY_UPDATED_AT="updated_at";
public static final String KEY_COLOR="color";
public static final String KEY_TAG="tag";
public static final String KEY_FAVOURITE="favourite";

public static final String KEY_LOCKSTATUS = "lock_status";
public static final String KEY_PASSCODE_ID="passcode_id";
public static final String KEY_PASSOCDE = "passcode";
public static final String KEY_PASSCODE_STATUS = "passcode_status";


private static final String CREATE_TABLE_NOTE="CREATE TABLE "
        +TABLE_NOTES+"("+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "
        +KEY_TITLE+" TEXT, "
        +KEY_CONTENT+" TEXT, "
        +KEY_UPDATED_AT+" BIGINT, "
        +KEY_COLOR+" INT, "
        +KEY_FAVOURITE+" INT, "
        +KEY_LOCKSTATUS+" INT"+" )";

private static final String CREATE_TABLE_PASSCODE="CREATE TABLE IF NOT EXISTS "
        +TABLE_PASSCODE+"("+KEY_PASSCODE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "
        +KEY_PASSOCDE+" TEXT"+" )";

public DatabaseHelper(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
    this.context = context;
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_NOTE);
    db.execSQL(CREATE_TABLE_PASSCODE);
}

public void onDowngrade(SQLiteDatabase paramSQLiteDatabase, int paramInt1, int paramInt2) {
    onUpgrade(paramSQLiteDatabase, paramInt1, paramInt2);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion < 3){
        db.execSQL("ALTER TABLE notes ADD COLUMN color INT");
        db.execSQL("ALTER TABLE notes ADD COLUMN favourite INT");
    }
    if (newVersion > oldVersion){
        db.execSQL("ALTER TABLE notes ADD COLUMN lock_status INT");
        db.execSQL("CREATE TABLE IF NOT EXISTS "
                +TABLE_PASSCODE+"("+KEY_PASSCODE_ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "
                +KEY_PASSOCDE+" TEXT"+" )");
    }
}

Answer 1

Your code as written now will try to add the column lock_status every time your database version increases. You have an if statement checking for old version < 3, then immediately have a second check for new version greater than old version. That second check is always going to be true when your version increases, so it will always try to add the column.

Suppose you added the lock_status column in version 4. Then a correct test would be

if (oldVersion < 4) {
    // your code goes here
}

The problem is not (necessarily) that you add lock_status in your create and in your upgrade, as suggested in a comment. If the database already exists, the create method will not be called. If the database does not exists yet, the update method will not be called.