搬移後下一筆為何重用了已刪編號？

先核來源與已訂規則

本例不得重用已產生編號。來源 AUTOINCREMENT 建過 1、2，再刪 2，實際序列仍 2。只拿尚存 1 重建普通主鍵會丟歷史；更正的受控重建須帶原序列。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"created":[1,2],"deleted":[2]}

下載可編輯 CSV ↓

比較兩條實際路徑

錯下一號為 2，更正為 3。程序拷貝前回讀來源 sqlite_sequence，僅在小型假設資料庫示範，不是直接修正式產生器。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":2,"label":"new"}]}
更正路徑	{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":3,"label":"new"}]}

下載可編輯 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('CREATE TABLE source(id INTEGER PRIMARY KEY AUTOINCREMENT,label TEXT);');
      for(const id of i.created)db.prepare('INSERT INTO source VALUES(?,?)').run(id,'old');
      for(const id of i.deleted)db.prepare('DELETE FROM source WHERE id=?').run(id);
      const sequence=db.prepare("SELECT seq FROM sqlite_sequence WHERE name='source'").get()?.seq??0;
      db.exec('CREATE TABLE target(id INTEGER PRIMARY KEY'+(corrected?' AUTOINCREMENT':'')+',label TEXT);INSERT INTO target SELECT * FROM source;');
      if(corrected){db.prepare("DELETE FROM sqlite_sequence WHERE name='target'").run();db.prepare('INSERT INTO sqlite_sequence(name,seq) VALUES(?,?)').run('target',sequence);}
      db.exec("INSERT INTO target(label) VALUES('new');");
      return {previousSequence:sequence,records:rows(db,'SELECT * FROM target ORDER BY id')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"created":[1,2],"deleted":[2]},"expected":{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":2,"label":"new"}]},"corrected":false},{"label":"corrected base","input":{"created":[1,2],"deleted":[2]},"expected":{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":3,"label":"new"}]},"corrected":true},{"label":"highest old row still present","input":{"created":[1,2],"deleted":[]},"expected":{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":2,"label":"old"},{"id":3,"label":"new"}]},"corrected":true},{"label":"empty no-history source","input":{"created":[],"deleted":[]},"expected":{"previousSequence":0,"records":[{"id":1,"label":"new"}]},"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 未刪，下一號自然也是 3；真正空且無歷史來源則由 1 起。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
最高編號行仍在	{"created":[1,2],"deleted":[]}	{"previousSequence":2,"records":[{"id":1,"label":"old"},{"id":2,"label":"old"},{"id":3,"label":"new"}]}
未有編號產生歷史	{"created":[],"deleted":[]}	{"previousSequence":0,"records":[{"id":1,"label":"new"}]}

下載可編輯 CSV ↓

尚存最高編號 1，足以代表此政策的歷史嗎？

查看參考答案

不足，已刪的 2 曾經產生。須帶序列 2，下一筆才得 3。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源