Android: SQLite - insertWithOnConflict

Question

I am calling insertWithOnConflict, using SQLiteDatabase.CONFLICT_IGNORE. However, when a conflict occurs "-1" is returned instead of the id of the existing row. How do I correct this?

Table creation:

EDIT :

String CREATE_CATEGORY_TABLE = "CREATE TABLE "+TABLE_CATEGORY+"(" +
    BaseColumns._ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
    KEY_CATEGORY_NAME+" TEXT UNIQUE" +
    ")";
db.execSQL(CREATE_CATEGORY_TABLE);

Insert statement:

ContentValues values = new ContentValues();
values.put(KEY_CATEGORY_NAME, name);
int catID = (int) db.insertWithOnConflict(TABLE_CATEGORY, null, values, SQLiteDatabase.CONFLICT_IGNORE);

Answer 1

insertWithOnConflict, using SQLiteDatabase.CONFLICT_IGNORE does not work as expected and should probably be avoided altogether, as per this issue:
https://code.google.com/p/android/issues/detail?id=13045

Answer 2

Android expects the primary key column to be called _id. Might be the cause: since the column does not exist, it can't return the value and returns -1.

Answer 3

SQLiteDatabase.CONFLICT_IGNORE comment is supposed to function the same way as you had observed. While trying to insert, if there is no conflicting row, then it will insert a new row with the given values and return the id of the newly inserted row. On the other hand if there is already a conflicting row (with same unique key), then the incoming values will be ignored and the existing row will be retained and then the return value will be -1 to indicate a conflict scenario. To know further details and to understand how to handle this return value, please read Android/SQLite: Insert-Update table columns to keep the identifier

Answer 4

The workaround that I used is using insertOrThrow instead of insertWithOnConflict, and explicitly catching SQLiteConstraintException. You need to explicitly catch it because it inherits from RuntimeException.

I had to use this workaround because insert was dumping the database scheme to the logs when there was a conflict, and that was unacceptable.