How to add an additional column in a database table in Sqlite?

Question

I have a SQLite table for students.

I want to add another column in students for student gender. I have below code to create my table.

How can I additional column "Gender" in my student's table?

private static final String TAG = "SyncStudents";

//Declare tables and fields where we will store all the amdins information.
private static final String TABLE_NAME = "studetns";
private static final String COL1 = "id";
private static final String COL2 = "firstname";
private static final String COL3 = "lastname";
private static final String COL4 = "age";
private static final String COL5 = "dob";
private static final String COL6 = "cir";
private static final String COL7 = "class";
private static final String COL8 = "address";
private static final String COL9 = "grade";


public SyncRoutesHelper(Context context) {
    super(context, TABLE_NAME, null, 1);
}


@Override
public void onCreate(SQLiteDatabase db) {

    //Create the table
    String createTable = "CREATE TABLE " + TABLE_NAME + " (" + COL1 + " INTEGER PRIMARY KEY, " + COL2 + " TEXT," + COL3 + " TEXT," + COL4 + " INTEGER," +
            COL5 + " INTEGER," + COL6 + " INTEGER," + COL7 + " TEXT," + COL8 + " TEXT," + COL9 + " INTEGER )";

    //Execute the above statement
    db.execSQL(createTable);
}

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("DROP IF TABLE EXISTS " + TABLE_NAME);
    onCreate(db);

    db.execSQL("ALTER TABLE students ADD COLUMN genger TEXT DEFAULT NULL");

} 

I tried to write below the line in "OnUpdate" method but it does not work.

db.execSQL("ALTER TABLE students ADD COLUMN gender TEXT DEFAULT NULL");

Answer 1

For onUpgrade to run you have to increase the version number which is the 4th paramter of the super call. So you need to change

super(context, TABLE_NAME, null, 1);

to

super(context, TABLE_NAME, null, 2);

However, as your onUpgrade method drops the table and calls onCreate you might as well just change the code in the onCreate method to include the new column. This will delete all existing data.

If you need to retain the existing data then the onUpgrade method should just have the single line to ALTER the table and be :-

@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
    db.execSQL("ALTER TABLE students ADD COLUMN gender TEXT DEFAULT NULL");
}

• Note that you should also change the code in the onCreate method to include the new column so that a new install will include the new column.

You may wish to have a look at How To: Android SQLite onUpgrade() as this goes into using the i and i1 values as you would get issues if you then introduced more schema changes and increased the version to 3 (and so on).