子行看似正確，為何報 foreign key mismatch？

先核來源與已訂規則

本例父 label 定 BINARY，錯唯一索引卻用 NOCASE，不是外鍵所需相符父鍵定義。建表可先成功，到子寫入才暴露跨表結構不符。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"parent":"A","child":"A"}

下載可編輯 CSV ↓

比較兩條實際路徑

更正唯一索引沿父鍵 BINARY，子 A 可連父 A。小寫 a 仍因此鍵分大小寫而找不到真正父。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"error":{"code":1},"parents":[{"label":"A"}],"children":[]}
更正路徑	{"error":null,"parents":[{"label":"A"}],"children":[{"parent_label":"A"}]}

下載可編輯 CSV ↓

本地 Node.js 程序：22.23.3 或 24.14.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(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);
}

下載可執行例子 ↓

下次載入前核邊界

更正結構下小寫 a 錯 787；沒有要求子寫入，父行照留。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
分大小寫後仍未配對	{"parent":"A","child":"a"}	{"error":{"code":787},"parents":[{"label":"A"}],"children":[]}
沒有要求子行	{"parent":"A","child":null}	{"error":null,"parents":[{"label":"A"}],"children":[]}

下載可編輯 CSV ↓

要修原例 mismatch，應否先改來源 A？

查看參考答案

不應，所附值有效。先修父表不相符的唯一索引定義，再寫子 A。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源