Thursday, September 28, 2017

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.


No comments:

Post a Comment