Is it OK to rely on an SQLiteConstraint and let SQLite do the checking for me? Is there a catch?

Question

I have an SQLite database in Android and I use a ContentProvider to handle the operations, which are persisted to a table with a UNIQUE qualifier in a column.

Problem:

However, when I insert duplicated values into the database, it doesn't break my code per se, but it still spits thousands of SQLiteConstraintException Log lines, and to my user that just feels like polluting the Log, something unpolished. I've tried catching the exception just to experiment, but it still logs.

Question:

So, how do I silent those log lines? Is that even possible?

Please read the comment below for the reason to ask the question.

Error:

Time column has the UNIQUE constraint:

Error inserting Factor=2.0 Time=1325375465000 Extra=none
android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
    at android.database.sqlite.SQLiteStatement.native_execute(Native Method)
    at android.database.sqlite.SQLiteStatement.execute(SQLiteStatement.java:55)
    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1549)
    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1410)
    at mypackage.myapp.provider.DataProvider.bulkInsert(DataProvider.java:353)
    at android.content.ContentProvider$Transport.bulkInsert(ContentProvider.java:179)
    at android.content.ContentResolver.bulkInsert(ContentResolver.java:646)
    at mypackage.myapp.service.MyService.onHandleIntent(MyService.java:96)
    at android.app.IntentService$ServiceHandler.handleMessage(IntentService.java:59)
    at android.os.Handler.dispatchMessage(Handler.java:99)
    at android.os.Looper.loop(Looper.java:123)
    at android.os.HandlerThread.run(HandlerThread.java:60)

Answer 1

If you can formulate or modify the SQL yourself, either for INSERT or for the initial CREATE TABLE, you can use SQLite's conflict handling extensions. There are two options for how to do this:

• When you insert, use INSERT OR IGNORE rather than just INSERT. You can also use OR REPLACE, OR ABORT, or any of several other reactions.
• When you create the table, specify an ON CONFLICT IGNORE clause for the UNIQUE constraint. That will cause inserts or updates which violate the constraint to silently do nothing.

I find the idea of using INSERT OR IGNORE/INSERT OR REPLACE to handle duplicate data, particularly in concurrent environments, to be very clean. It checks for duplication once - in the database - and avoids race conditions where you check for existence first (admittedly not a problem if only one process/thread is accessing the database).

However, if the duplicates are a result of a bug (rather than duplicate events/actions that your code is just not explicitly de-duplicating), then this could just be hiding the bug rather than fixing it. Lack of explicit de-dulication, however, is not a bug in my opinion. So if the fix is to check for duplicates, use the database; if the real problem is that they were generated in the first place (at the actual application level, not the database row level), then I would probably look for that problem.