Inserting data to SQLite table with constraint failure

Question

I'm trying to insert data into SQLite table on Android. _id is primary key of the table and I am inserting a row using this method:

public void addSomeData(int id, String datetime) {
    ContentValues contentValues = new ContentValues();
    contentValues.put(KEY_ID, id);
    contentValues.put(KEY_DATETIME, datetime);
    mDb.insert(TABLE, null, contentValues);
}

The problem I get is that sometimes primary key constraint is validated and I would like to use something like INSERT IF NOT EXISTS, but preferably something that would work with ContentValues. What are my options? I understand that insertOrThrow() and insertWithOnConflict() methods only return different values, or should I use one of these methods?

score 1 · Answer 1 · answered Dec 12 '13 at 16:50

1

Use insertWithOnConflict() with CONFLICT_IGNORE.

Will return ROWID/primary key of new or existing row, -1 on any error.

answered Dec 12 '13 at 16:50

LS_ᴅᴇᴠ

10,823
1
23
46

score 0 · Answer 2 · answered Dec 12 '13 at 16:49

0

In my case "constraint failure" happened because of I had some tables which are depended on each other. As for the "insert if not exist", you can query with this id and you check if the cursor's count is bigger than zero. Check the method I'm already using in my app.

public boolean isRowExists(long rowId) {

    Cursor cursor = database.query(this.tableName, this.columns, DBSQLiteHelper.COLUMN_ID + " = ? ", new String[] { "" + rowId }, null, null, null);

    int numOfRows = cursor.getCount();
    cursor.close();

    return (numOfRows > 0) ? true : false;
}

answered Dec 12 '13 at 16:49

osayilgan

5,873
7
47
68

@REACHUS This is what you are looking for. I just saw it when I was looking for the answer for you. I will update some of my large queries as well. http://stackoverflow.com/a/4330694/1080954 – osayilgan Dec 13 '13 at 09:32
Thanks. How should I perform INSERT/UPDATE if I have `primary key` set in my table? – syntagma Dec 13 '13 at 10:16
@REACHUS did you check out the link I just commented ? The answer is there. Check this link too. This might help as well. http://stackoverflow.com/a/690665/1080954 – osayilgan Dec 13 '13 at 10:27

score 0 · Answer 3 · answered Dec 12 '13 at 16:50

0

to do so you could simply query the db to see if a row with that key exists and insert the new row only if the query returns no data.

answered Dec 12 '13 at 16:50

Pasquale Anatriello

2,355
1
16
16

I know but I'm inserting many rows and that wouldn't be efficient. – syntagma Dec 12 '13 at 16:56
@REACHUS This is exactly what the database does to check the constraint for every record (and after the check, that record is in the cache). – CL. Dec 12 '13 at 19:16
What about using the `insertWithOnConflict()`, as suggested in one of the other answers? – syntagma Dec 12 '13 at 20:51

Inserting data to SQLite table with constraint failure

3 Answers3