Rethinavel Pillai Rethinavel Pillai - 26 days ago 15
Android Question

Error while copying records from table1 to table 2. sqlite constraint exception error code 19 constraint failed

I'm copying all the records (back up) from table1 to table2 in the same database. This is my first database application.

Here is the table 1 structure :

private static final String CREATE_SELECTED_CAND_TABLE = " CREATE TABLE if not exists "
+ TABLE_SELECTED_CAND
+ "("
+ KEY_ID
+ " integer PRIMARY KEY autoincrement,"
+ KEY_NAME1
+ " TEXT,"
+ KEY_NAME2
+ " TEXT,"
+ KEY_NAME3
+ " TEXT,"
+ KEY_NAME4
+ " TEXT," + KEY_NAME5 + " TEXT" + ")";


Table2 Structure :

private static final String CREATE_SELECTED_CAND_RECOUNT1 = " CREATE TABLE if not exists "
+ TABLE_RECOUNT1
+ "("
+ KEY_ID
+ " integer PRIMARY KEY autoincrement,"
+ KEY_NAME1
+ " TEXT,"
+ KEY_NAME2
+ " TEXT,"
+ KEY_NAME3
+ " TEXT,"
+ KEY_NAME4
+ " TEXT," + KEY_NAME5 + " TEXT" + ")";


and I am copying like this :

mDb.execSQL(" INSERT INTO " + TABLE_RECOUNT1 + " SELECT * FROM "
+ TABLE_SELECTED_CAND);


Now the problem is, It is copying only if all the columns are not null I have some columns as null, but I want to copy them also. How do I achieve this.? Any help would be highly appreciable..!

These are the stack traces :


E/AndroidRuntime(30294):
android.database.sqlite.SQLiteConstraintException: error code 19:
constraint failed


E/AndroidRuntime(30294): at android.database.sqlite.SQLiteStatement.native_execute(Native Method)

E/AndroidRuntime(30294): at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:92)

E/AndroidRuntime(30294): at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:2028)

E/AndroidRuntime(30294): at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1968)

E/AndroidRuntime(30294): at com.dana.db.SelectedCandidatesDB.copyToRecount1(SelectedCandidatesDB.java:257)

E/AndroidRuntime(30294): at com.dana.votecount.VoteRecount.fetchAllFromRecount1(VoteRecount.java:161) E/AndroidRuntime(30294): at com.dana.votecount.VoteRecount.access$1(VoteRecount.java:158)

E/AndroidRuntime(30294): at com.dana.votecount.VoteRecount$2$1.onClick(VoteRecount.java:107)

CL. CL.
Answer

It is actually possible to set autoincrement columns to a specific value, but only if that value does not yet exist.

It is likely that the records already exist in the RECOUNT1 table.

To ensure that there are no old records, delete them before inserting:

mDb.execSQL("DELETE FROM " + TABLE_RECOUNT1);
mDb.execSQL("INSERT INTO " + TABLE_RECOUNT1 + " SELECT ...");

Alternatively, if you know that none of the records have changed, you can use the INSERT OR IGNORE command to insert only those records whose IDs do not yet exist in the target table:

mDb.execSQL("INSERT OR IGNORE INTO " + TABLE_RECOUNT1 + " SELECT ...");