替換父紀錄為何刪了子紀錄歷程？

先核來源與已訂規則

本例父 1 有子 L，並定 ON DELETE CASCADE。新 New 只改父名稱，子 L 要留。INSERT OR REPLACE 先移除衝突舊父再插替代行，便級聯刪 L；更正 UPSERT 只更新名稱。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"id":1,"name":"New","childPresent":true}

下載可編輯 CSV ↓

比較兩條實際路徑

更正父 1 是 New，子 L 仍相連。真正新父 2 可插入，不影響父 1 的子行。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"parents":[{"id":1,"name":"New"}],"children":[]}
更正路徑	{"parents":[{"id":1,"name":"New"}],"children":[{"code":"L","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=ON;CREATE TABLE parent(id INTEGER PRIMARY KEY,name TEXT);CREATE TABLE child(code TEXT,parent_id INTEGER REFERENCES parent(id) ON DELETE CASCADE);INSERT INTO parent VALUES(1,'Old');");
      if(i.childPresent)db.exec("INSERT INTO child VALUES('L',1);");
      db.prepare(corrected?'INSERT INTO parent VALUES(?,?) ON CONFLICT(id) DO UPDATE SET name=excluded.name':'INSERT OR REPLACE INTO parent VALUES(?,?)').run(i.id,i.name);
      return {parents:rows(db,'SELECT * FROM parent ORDER BY id'),children:rows(db,'SELECT * FROM child')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"id":1,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"New"}],"children":[]},"corrected":false},{"label":"corrected base","input":{"id":1,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"New"}],"children":[{"code":"L","parent_id":1}]},"corrected":true},{"label":"genuinely new parent","input":{"id":2,"name":"New","childPresent":true},"expected":{"parents":[{"id":1,"name":"Old"},{"id":2,"name":"New"}],"children":[{"code":"L","parent_id":1}]},"corrected":true},{"label":"existing parent without child","input":{"id":1,"name":"New","childPresent":false},"expected":{"parents":[{"id":1,"name":"New"}],"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);
}

下載可執行例子 ↓

下次載入前核邊界

新父 2 時保舊 1/L；原父沒有子行也照常更新。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
真正新父紀錄	{"id":2,"name":"New","childPresent":true}	{"parents":[{"id":1,"name":"Old"},{"id":2,"name":"New"}],"children":[{"code":"L","parent_id":1}]}
父紀錄沒有子行	{"id":1,"name":"New","childPresent":false}	{"parents":[{"id":1,"name":"New"}],"children":[]}

下載可編輯 CSV ↓

此處 REPLACE 與更新現有行是否等同？

查看參考答案

不等同，替換會刪舊父並觸發級聯。要保 L，須用本意的更新。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源