已開外鍵檢查，這次匯入為何仍未受保護？

先核來源與已訂規則

本例明確先設 foreign_keys OFF，不靠 Node 連線預設。BEGIN 後才改 ON，在該交易內不生效。所需路徑要先 ON 才 BEGIN，回讀設定，無父紀錄的子寫入須失敗。

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

下載可編輯 CSV ↓

比較兩條實際路徑

錯結果仍 enabled0，卻存了孤立子行。更正是 1、實際外鍵錯 787，交易回復後沒有已提交子行。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"enabled":0,"error":null,"children":[{"id":1,"parent_id":1}]}
更正路徑	{"enabled":1,"error":{"code":787},"children":[]}

下載可編輯 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(corrected){db.exec('PRAGMA foreign_keys=ON;');if(db.prepare('PRAGMA foreign_keys').get().foreign_keys!==1)throw new Error('Required enforcement unavailable');}db.exec('BEGIN;');
      if(!corrected)db.exec('PRAGMA foreign_keys=ON;');
      const error=failed(()=>db.prepare('INSERT INTO child VALUES(1,?)').run(i.parentId));
      db.exec(error?'ROLLBACK':'COMMIT');
      return {enabled:db.prepare('PRAGMA foreign_keys').get().foreign_keys,error,children:rows(db,'SELECT * FROM child')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"parentPresent":false,"parentId":1},"expected":{"enabled":0,"error":null,"children":[{"id":1,"parent_id":1}]},"corrected":false},{"label":"corrected base","input":{"parentPresent":false,"parentId":1},"expected":{"enabled":1,"error":{"code":787},"children":[]},"corrected":true},{"label":"existing parent permits child","input":{"parentPresent":true,"parentId":1},"expected":{"enabled":1,"error":null,"children":[{"id":1,"parent_id":1}]},"corrected":true},{"label":"explicitly optional parent","input":{"parentPresent":false,"parentId":null},"expected":{"enabled":1,"error":null,"children":[{"id":1,"parent_id":null}]},"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);
}

下載可執行例子 ↓

下次載入前核邊界

父紀錄對得上就可寫子行；此結構明確選填的 null 父連結也可收。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
有相符父紀錄	{"parentPresent":true,"parentId":1}	{"enabled":1,"error":null,"children":[{"id":1,"parent_id":1}]}
明訂選填連結	{"parentPresent":false,"parentId":null}	{"enabled":1,"error":null,"children":[{"id":1,"parent_id":null}]}

下載可編輯 CSV ↓

BEGIN 後發 PRAGMA ON，便證明檢查已啟用嗎？

查看參考答案

不是，錯例連線仍 OFF。交易前設定並回讀 ON，欠父寫入才會被拒。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源