How to add a new column to SQLite database for Android?

Question

I have one problem with Android SQLite database.

I have one table that contains one .StudentFname field and this application works fine with Android 2.3.1, and now, if I add another field, my application does not work properly.

Can someone help me who knows the database very well,

Answer 1

you can use the ALTER TABLE function in your onUpgrade() method, for example:

 @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, SQLite will differ depending on the definition of the column.

Answer 2

The easiest way to do this is to add the SQL to the onUpgrade() method SQL to the onUpgrade() 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"); } } 

Answer 3

I came across this question when I needed help in my application, but I saw problems with many 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 that the code will work when users update more than one version and that only one update is performed in the update instruction. To learn a little about this, check out this blog .

Answer 4

Perhaps a slightly more elegant approach using a switch instead of upgrading from any circuit to the latest circuit ...

Here is also a decent page in the syntax for modifying 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 } } 

Answer 5

@ Aamirkhan.i I think that you solved the problem that you mentioned in the comments a long time ago. I think you have not increased the database version. or the answers here are straightforward. I am writing this because it can help anyone who has not increased or changed the version number of the database when changing the table.

Answer 6

I think we should not check this condition.

  if (newVersion > oldVersion) { } 

because if we use it, it means that every time we increase the version of the database, then onUpdrade () will call and the condition will be true, so we need to check this as

 if(oldVersion==1) { } 

therefore, in this case, if the old version is 2, the condition will be false, and the user with the old version 2 will not update the database (which user wants only for version 1), because the users have already been updated for this database.

Answer 7

I made the following approach, it was replaced for me

if DB version: 6

 Ex : There is a table with 5 columns 

When upgrading to: 7 (I add 1 new column to 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 a request.

 EX: public static final String DATABASE_ALTER_ADD_PAPER_PAID = "ALTER TABLE " + TableConstants.MY_PAPERS_TABLE + " ADD COLUMN " + COLUMN_PAPER_PAID + " TEXT;"; 

After the above two operations, it will work perfectly for a new user installing and updating the application.

Answer 8

I found a link while searching for the same problem. It explains how to use the update. https://thebhwgroup.com/blog/how-android-sqlite-onupgrade

this explains why use this code below

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

Answer 9

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

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

Answer 10

To add a new column to the table, you need to use ALTER . In android, you can add a new column inside onUpgrade() .

You might be wondering how onUpgrade() will add a new column?

When implementing a subclass of SQLiteOpenHelper you need to call the constructor of the superclass: super(context, DB_NAME, null, 1); in your class constructor. There I went 1 for the version.

When I changed version 1 to higher ( 2 or more), onUpgrade() called. And do the SQL modifications that I intend to do. My class constructor after version change:

 public DatabaseHelper(Context context) { super(context, DB_NAME, null, 2);//version changed from 1 to 2 } 

SQL modifications are checked as follows: the superclass constructor compares the version of the stored SQLite db file with the version that I passed super() . If these (previous and now) version numbers are different, onUpgrade() is called.

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