啟用外鍵後，舊孤立紀錄為何仍在？

先核來源與已訂規則

本例舊載入明確在 OFF 下進行。再 ON 可檢查之後相關寫入，卻不會修好原先指向欠缺父 1 的子行。此正式使用門檻要 foreign_key_check 沒違規。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"parentPresent":false,"childPresent":true}

下載可編輯 CSV ↓

比較兩條實際路徑

更正暫停使用，列 child 第一行、父表及外鍵索引 0；保留舊行供修正，不聲稱改設定後它已消失。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"released":true,"violations":null,"children":[{"id":1,"parent_id":1}]}
更正路徑	{"released":false,"violations":[{"table":"child","rowid":1,"parent":"parent","fkid":0}],"children":[{"id":1,"parent_id":1}]}

下載可編輯 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=OFF;CREATE TABLE parent(id INTEGER PRIMARY KEY);CREATE TABLE child(id INTEGER PRIMARY KEY,parent_id INTEGER REFERENCES parent(id));');
      if(i.parentPresent)db.exec('INSERT INTO parent VALUES(1);');if(i.childPresent)db.exec('INSERT INTO child VALUES(1,1);');
      db.exec('PRAGMA foreign_keys=ON;');const violations=corrected?rows(db,'PRAGMA foreign_key_check'):null;
      return {released:!corrected||violations.length===0,violations,children:rows(db,'SELECT * FROM child')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"parentPresent":false,"childPresent":true},"expected":{"released":true,"violations":null,"children":[{"id":1,"parent_id":1}]},"corrected":false},{"label":"corrected base","input":{"parentPresent":false,"childPresent":true},"expected":{"released":false,"violations":[{"table":"child","rowid":1,"parent":"parent","fkid":0}],"children":[{"id":1,"parent_id":1}]},"corrected":true},{"label":"historical child has matching parent","input":{"parentPresent":true,"childPresent":true},"expected":{"released":true,"violations":[],"children":[{"id":1,"parent_id":1}]},"corrected":true},{"label":"no historical children","input":{"parentPresent":false,"childPresent":false},"expected":{"released":true,"violations":[],"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);
}

下載可執行例子 ↓

下次載入前核邊界

舊父子已相符可過；沒有歷史子行也沒有違規。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
舊連結有父紀錄	{"parentPresent":true,"childPresent":true}	{"released":true,"violations":[],"children":[{"id":1,"parent_id":1}]}
沒有歷史子行	{"parentPresent":false,"childPresent":false}	{"released":true,"violations":[],"children":[]}

下載可編輯 CSV ↓

只見 ON，可否確認舊載入行都符合？

查看參考答案

不可，要查歷史行。本例仍有一項違規，更正前不能正式使用。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源