Read the source and its declared rule
The original complete batch lists child 1 before parent 1. Its authorised target uses a deferred FK within one explicit transaction. Immediate checking rejects the first child before the later parent can satisfy it; deferral changes checking time, not the required final relationship.
| Record | Input JSON |
|---|---|
| Base input | {"transaction":true,"parentPresent":true} |
Compare the actual paths
Good complete batch commits both rows. If the parent remains missing, COMMIT fails 787 and the fixture rolls back, leaving neither row committed.
| Path | Output JSON |
|---|---|
| Misleading path | {"error":{"code":787},"parents":[],"children":[]} |
| Corrected path | {"error":null,"parents":[{"id":1}],"children":[{"id":1,"parent_id":1}]} |
import {DatabaseSync} from 'node:sqlite';
import {TextDecoder} from 'node:util';
import {inflateSync} from 'node:zlib';
import {posix} from 'node:path';
import {createHash} from 'node:crypto';
const rows=(db,sql)=>db.prepare(sql).all().map(r=>({...r}));
const failed=(action)=>{try{action();return null;}catch(e){return {code:e.errcode??e.code??e.name};}};
import assert from 'node:assert/strict';
const run=function run(i,corrected=true){
const db=new DatabaseSync(':memory:');try{db.exec('PRAGMA foreign_keys=ON;CREATE TABLE parent(id INTEGER PRIMARY KEY);CREATE TABLE child(id INTEGER PRIMARY KEY,parent_id INTEGER REFERENCES parent(id)'+(corrected?' DEFERRABLE INITIALLY DEFERRED':'')+');');
if(i.transaction)db.exec('BEGIN;');
const error=failed(()=>{db.exec('INSERT INTO child VALUES(1,1);');if(i.parentPresent)db.exec('INSERT INTO parent VALUES(1);');if(i.transaction)db.exec('COMMIT;');});
if(error&&i.transaction)db.exec('ROLLBACK;');
return {error,parents:rows(db,'SELECT * FROM parent'),children:rows(db,'SELECT * FROM child')};
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"transaction":true,"parentPresent":true},"expected":{"error":{"code":787},"parents":[],"children":[]},"corrected":false},{"label":"corrected base","input":{"transaction":true,"parentPresent":true},"expected":{"error":null,"parents":[{"id":1}],"children":[{"id":1,"parent_id":1}]},"corrected":true},{"label":"missing parent at commit","input":{"transaction":true,"parentPresent":false},"expected":{"error":{"code":787},"parents":[],"children":[]},"corrected":true},{"label":"no deferred window outside transaction","input":{"transaction":false,"parentPresent":true},"expected":{"error":{"code":787},"parents":[],"children":[]},"corrected":true}];
for(const test of cases){
const actual=run(test.input,test.corrected);
assert.deepEqual(actual,test.expected,test.label);
console.log(test.label,actual);
}
Check boundaries before the next load
Missing final parent fails at commit. Without an explicit transaction, the child has no deferred window and fails immediately.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Parent missing at commit | {"transaction":true,"parentPresent":false} | {"error":{"code":787},"parents":[],"children":[]} |
| No explicit transaction window | {"transaction":false,"parentPresent":true} | {"error":{"code":787},"parents":[],"children":[]} |
Does deferred mean the missing parent may remain after commit?
Show the worked answer
No. Parent 1 must exist by commit. Deferral permits the stated temporary order only inside the transaction.