_id conflict when backing up tables in onUpgrade

Question

I want to persist table data across database structure changes, so the basic flow is as follows:

create temp table
populate temp table with data from primary table
drop primary table
onCreate() is called
populate primary table with data from temp table
drop temp table

Steps 1 - 4 execute as expected, but I get a Unique Constraint Exception on primary_table._id when repopulating it after onCreate() is called.

In onUpgrade, I have the following structure:

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    db.execSQL("create table CustomerTemp as select * from Customer;");

    db.execSQL("insert into CustomerTemp select * from Customer;");

    db.execSQL("drop table if exists Customer;");

    onCreate(db);//create table customer...

    //this line throws Unique Constraint Exception on Customer._id
    db.execSQL("insert into  Customer select * from CustomerTemp ;");

    db.execSQL("drop table if exists CustomerTemp;");

}

I do not understand why an exception is being thrown only for re-populating the primary table, but not when the temp table is being populated.

score 1 · Accepted Answer · answered Jun 26 '17 at 07:01

db.execSQL("create table CustomerTemp as select * from Customer;");

This command creates the CustomerTemp table, and copies all rows into it.

db.execSQL("insert into CustomerTemp select * from Customer;");

This command copies all rows again. The CREATE TABLE AS statement does not recreate any constraints (such as PRIMARY KEY or UNIQUE), so the duplicates do not yet result in an error.

_id conflict when backing up tables in onUpgrade

1 Answers1