Gist for https://github.com/novoda/SQLiteProvider/issues/24

1_initial_schema.sql

-- ...

CREATE TABLE "image_likers" (
    _id INTEGER PRIMARY KEY AUTOINCREMENT,
    image_id INTEGER,
    user_id INTEGER,

    UNIQUE(image_id, user_id) ON CONFLICT REPLACE,
    FOREIGN KEY (image_id) REFERENCES "images" (_id),
    FOREIGN KEY (user_id) REFERENCES "users" (_id)
);

-- ...

DBUtils.java

// ...

    public static List<String> getUniqueConstrains(SQLiteDatabase db, String table) {
        List<String> constrains = new ArrayList<String>();
        final Cursor pragmas = db.rawQuery(String.format(PRGAMA_INDEX_LIST, table), null);
        while (pragmas.moveToNext()) {
            int isUnique = pragmas.getInt(2);
            if (isUnique == 1) {
                String name = pragmas.getString(1);
                final Cursor pragmaInfo = db.rawQuery(String.format(PRGAMA_INDEX_INFO, name), null);
                while (pragmaInfo.moveToNext()) {
                    constrains.add(pragmaInfo.getString(2));
                }
                pragmaInfo.close();
            }
        }
        pragmas.close();
        return constrains;
    }
    
// ...

InsertHelper.java

// ...

    public long insert(Uri uri, ContentValues values) {
        ContentValues insertValues = (values != null) ? new ContentValues(values) : new ContentValues();
        final String table = UriUtils.getItemDirID(uri);
        final List<String> uniqueConstrains = dbHelper.getUniqueConstrains(table);
        appendParentReference(uri, insertValues);
        long rowId = -1;

        if (uniqueConstrains.size() > 1) {
            // There are two or more constraints. Trying an insert with explicitely stating that no
            // conflict resolution is applied to the insert statement, which then should use the
            // conflict resolution provided in the table's SQL. If none, the default resolution
            // (ABORT) is used.

            // NOTE: Using CONFLICT_IGNORE does NOT return the ID of the existing row, as stated
            // in Androids documentation and therefore should be avoided.
            // See https://code.google.com/p/android/issues/detail?id=13045
            // See https://code.google.com/p/android/issues/detail?id=59244

            rowId = dbHelper.getWritableDatabase().insertWithOnConflict(
                    table, null, insertValues, SQLiteDatabase.CONFLICT_NONE);

            if (rowId < 0 && Log.Provider.warningLoggingEnabled()) {
                Log.Provider.w("Conflict occurred at insert against URI: " + uri
                        + ". Please use \"ON CONFLICT REPLACE\" as conflict resolution for your constraints.");
            }
        } else if (uniqueConstrains.size() == 1) {
            rowId = tryUpdateWithConstrain(table, uniqueConstrains.get(0), insertValues);
        } else {
            if (Log.Provider.warningLoggingEnabled()) {
                Log.Provider.w("No constrain against URI: " + uri);
            }
        }
        if (rowId <= 0) {
            rowId = dbHelper.getWritableDatabase().insert(table, null, insertValues);
        }
        if (rowId > 0) {
            return rowId;
        }
        throw new SQLException("Failed to insert row into " + uri);
    }

// ...