How to update an SQLite Database and NOT lose all existing data?

Question

I'm adding a table to my app's SQLite DB. All my syntax there is fine, not the issue. But I'm having some trouble getting the new table to be created properly. I added the new table....

@Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(DATABASE_CREATE);
            db.execSQL(CREATE_REQUESTS);
            db.execSQL(CREATE_OVERRIDE);
        }

My on create method. I have 3 tables. When I updated the version number, I got an error saying "table requests (referring to CREATE_REQUESTS) has already been created." A look at my onUpgrade method...

@Override 
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS contacts");
            onCreate(db);
        }

Led me to understand that the line db.execSQL("DROP TABLE IF EXISTS contacts"), which refers to my DATABASE_CREATE table in the onCreate method, is used to drop the old table, then the next line, onCreate(db); recreates it. I did not add requests into that execSQL line, which is what caused the error. Here is the issue: I would rather not lose the data in the two tables I already have. Is there a way to add a table like I am trying to do, and not lose all the old data? Thanks.

Answer 1

You can do anything you want in onUpgrade. You can use ALTER to add new columns to your table.

Worst case, if your schema is completely and entirely different, you'll have to create the new table, populate it using data from the old table, and then delete the old table.

In any case, onUpgrade was designed to allow for a smooth upgrade without any loss of data. It's just up to you to implement it properly.