Read the source and its declared rule
The original parent label declares BINARY. Its bad unique index instead uses NOCASE, which is not the matching parent-key definition required by the FK. Creating the tables can succeed before a child write reveals the cross-table schema mismatch.
| Record | Input JSON |
|---|---|
| Base input | {"parent":"A","child":"A"} |
Compare the actual paths
Corrected unique index matches the declared BINARY parent key, so childA links to parentA. Lowercasea still fails actual existence because this key is case-sensitive.
| Path | Output JSON |
|---|---|
| Misleading path | {"error":{"code":1},"parents":[{"label":"A"}],"children":[]} |
| Corrected path | {"error":null,"parents":[{"label":"A"}],"children":[{"parent_label":"A"}]} |
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(label TEXT COLLATE BINARY);CREATE UNIQUE INDEX parent_key ON parent(label'+(corrected?'':' COLLATE NOCASE')+');CREATE TABLE child(parent_label TEXT REFERENCES parent(label));');
db.prepare('INSERT INTO parent VALUES(?)').run(i.parent);
const error=i.child===null?null:failed(()=>db.prepare('INSERT INTO child VALUES(?)').run(i.child));
return {error,parents:rows(db,'SELECT * FROM parent'),children:rows(db,'SELECT * FROM child')};
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"parent":"A","child":"A"},"expected":{"error":{"code":1},"parents":[{"label":"A"}],"children":[]},"corrected":false},{"label":"corrected base","input":{"parent":"A","child":"A"},"expected":{"error":null,"parents":[{"label":"A"}],"children":[{"parent_label":"A"}]},"corrected":true},{"label":"case-sensitive parent mismatch","input":{"parent":"A","child":"a"},"expected":{"error":{"code":787},"parents":[{"label":"A"}],"children":[]},"corrected":true},{"label":"no requested child write","input":{"parent":"A","child":null},"expected":{"error":null,"parents":[{"label":"A"}],"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
Lowercasea fails 787 under the corrected schema. No requested child write leaves the valid parent alone.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Case-sensitive unmatched value | {"parent":"A","child":"a"} | {"error":{"code":787},"parents":[{"label":"A"}],"children":[]} |
| No requested child | {"parent":"A","child":null} | {"error":null,"parents":[{"label":"A"}],"children":[]} |
Should sourceA be changed to repair the base mismatch?
Show the worked answer
No. The supplied value is valid; repair the parent’s incompatible unique-index definition, then write childA.