Dealing with circular data dependencies in SQLite-based content providers

By default SQLite does not defer enforcing foreign key constraints.  This means that inserting into tables that circularly depend on each other does not work by default even if insertions are scoped by a transaction. There are two options to make insertions scoped by a transaction work:

• Declare foreign key constraints as DEFERRABLE INITIALLY DEFERRED
• Execute PRAGMA defer_foreign_keys=true at the beginning of each transaction that deals with circular dependencies

When using ContentProvider API, applyBatch() is the only option to deal with circular data dependencies.  By default applyBatch() method does not scope individual operations by a transaction.  SQLite-based content provider should overwrite applyBatch() method to run the operations in a transaction scope:

@NonNull

@Override

public ContentProviderResult[] applyBatch(

    final @NonNull ArrayList<ContentProviderOperation> operations) 

    throws OperationApplicationException {

    final SQLiteDatabase db = dbHelper.getWritableDatabase();

    db.beginTransaction();

    db.execSQL("PRAGMA defer_foreign_keys=true;");

    try {

        final ContentProviderResult[] results =

            super.applyBatch(operations);

        db.setTransactionSuccessful();

        return results;

    } finally {

        db.endTransaction();

    }

}

Worth mentioning: ContentProvider.applyBatch() calls individual methods, such as insert(), update(), and delete().  If the individual methods are already scoped by transactions, we'll end up with nested transactions. The individual methods can use SQLiteDatabase.inTransaction() to detect this situation if nested transactions are not desired.