Delaying enforcement of foreign key constraints until the end of the outermost transaction

Inserting two rows that circularly depend on each other fails after first insert because of foreign key constraint violation:  java.lang.AssertionError: expected:<1> but was:<-1>

@Test
public void testCycle() {
    db.execSQL("pragma foreign_keys=true");
    db.execSQL("create table A(id integer primary key, bId integer references B(id))");
    db.execSQL("create table B(id integer primary key, aId integer references A(id))");
    final ContentValues cvA = new ContentValues();
    cvA.put("id", 1);
    cvA.put("bId", 2);
    final long aId = db.insert("A", null, cvA);
    assertEquals(1, aId);
    final ContentValues cvB = new ContentValues();
    cvB.put("id", 2);
    cvB.put("aId", 1);
    final long bId = db.insert("B", null, cvB);
    assertEquals(2, bId);
}

Scoping by a transaction does not help, we are getting the same error.

@Test
public void testCycle() {
    db.execSQL("pragma foreign_keys=true");
    db.execSQL("create table A(id integer primary key, bId integer references B(id))");
    db.execSQL("create table B(id integer primary key, aId integer references A(id))");
    db.beginTransaction();
    try {
        final ContentValues cvA = new ContentValues();
        cvA.put("id", 1);
        cvA.put("bId", 2);
        final long aId = db.insert("A", null, cvA);
        assertEquals(1, aId);
        final ContentValues cvB = new ContentValues();
        cvB.put("id", 2);
        cvB.put("aId", 1);
        final long bId = db.insert("B", null, cvB);
        assertEquals(2, bId);
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }
}

Appending "deferrable initially deferred" after "... bId integer references B(id)" fixes the second test.  Another option is to issue "PRAGMA defer_foreign_keys=true" right after the beginning of a transaction.

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.

Enforcing foreign key constraints in SQLite-based content providers

By default SQLite does not enforce foreign key constraints starting since version 3.6.19.  To change this behavior we need to run PRAGMA foreign_keys=true when configuring the database:

class MyDbHelper extends SQLiteOpenHelper {
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.execSQL("PRAGMA foreign_keys=true");
    }
}

Click here for more information.