複合參考只填一半，為何外鍵仍過？

先核來源與已訂規則

本例選填連結要麼 site、parentId 全空，要麼全填且對得上。SQLite 複合鍵有一項 NULL 就毋須匹配，所以 North/null 可過外鍵，仍違此全填全空規則。更正另加 CHECK。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"site":"North","parentId":null}

下載可編輯 CSV ↓

比較兩條實際路徑

更正半填連結錯 275，不建子行；全空仍是合法無連結紀錄，不會虛構父行。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"error":null,"children":[{"site":"North","parent_id":null}]}
更正路徑	{"error":{"code":275},"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=ON;CREATE TABLE parent(site TEXT,id INTEGER,PRIMARY KEY(site,id));INSERT INTO parent VALUES('North',1);CREATE TABLE child(site TEXT,parent_id INTEGER,FOREIGN KEY(site,parent_id) REFERENCES parent(site,id)"+(corrected?',CHECK((site IS NULL AND parent_id IS NULL) OR(site IS NOT NULL AND parent_id IS NOT NULL))':'')+');');
      const error=failed(()=>db.prepare('INSERT INTO child VALUES(?,?)').run(i.site,i.parentId));return {error,children:rows(db,'SELECT * FROM child')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"site":"North","parentId":null},"expected":{"error":null,"children":[{"site":"North","parent_id":null}]},"corrected":false},{"label":"corrected base","input":{"site":"North","parentId":null},"expected":{"error":{"code":275},"children":[]},"corrected":true},{"label":"legitimate entirely absent link","input":{"site":null,"parentId":null},"expected":{"error":null,"children":[{"site":null,"parent_id":null}]},"corrected":true},{"label":"complete matching pair","input":{"site":"North","parentId":1},"expected":{"error":null,"children":[{"site":"North","parent_id":1}]},"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 可收；完整 North/1 對真正父行，也可收。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
選填連結全空	{"site":null,"parentId":null}	{"error":null,"children":[{"site":null,"parent_id":null}]}
完整相符配對	{"site":"North","parentId":1}	{"error":null,"children":[{"site":"North","parent_id":1}]}

下載可編輯 CSV ↓

外鍵收件是否證明兩個參考部分都已填好？

查看參考答案

不是，North/null 會免複合匹配，原本全填或全空要求要另核。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源