Read the source and its declared rule
The original source code is NOT NULL and UNIQUE. CREATE TABLE target AS SELECT copies its values but not those constraints. The migration must explicitly create the constrained target, copy rows and test an actual duplicate write.
| Record | Input JSON |
|---|---|
| Base input | {"source":["A"],"incoming":"A"} |
Compare the actual paths
Bad target stores twoA rows. Corrected duplicateA fails UNIQUE2067 while the firstA remains; matching row counts before the probe would not reveal the lost protection.
| Path | Output JSON |
|---|---|
| Misleading path | {"error":null,"records":[{"code":"A"},{"code":"A"}]} |
| Corrected path | {"error":{"code":2067},"records":[{"code":"A"}]} |
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(code TEXT NOT NULL UNIQUE);');
for(const code of i.source)db.prepare('INSERT INTO source VALUES(?)').run(code);
if(corrected)db.exec('CREATE TABLE target(code TEXT NOT NULL UNIQUE); INSERT INTO target SELECT code FROM source;');
else db.exec('CREATE TABLE target AS SELECT * FROM source;');
const error=failed(()=>db.prepare('INSERT INTO target VALUES(?)').run(i.incoming));
return {error,records:rows(db,'SELECT code FROM target ORDER BY code')};
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"source":["A"],"incoming":"A"},"expected":{"error":null,"records":[{"code":"A"},{"code":"A"}]},"corrected":false},{"label":"corrected base","input":{"source":["A"],"incoming":"A"},"expected":{"error":{"code":2067},"records":[{"code":"A"}]},"corrected":true},{"label":"new unique source value","input":{"source":["A"],"incoming":"B"},"expected":{"error":null,"records":[{"code":"A"},{"code":"B"}]},"corrected":true},{"label":"empty source table","input":{"source":[],"incoming":"A"},"expected":{"error":null,"records":[{"code":"A"}]},"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);
}
Check boundaries before the next load
NewB is allowed besideA. An initially empty source still creates a target with its unique constraint.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Different new code | {"source":["A"],"incoming":"B"} | {"error":null,"records":[{"code":"A"},{"code":"B"}]} |
| Empty source retains target constraint | {"source":[],"incoming":"A"} | {"error":null,"records":[{"code":"A"}]} |
Does copying all existing rows preserve UNIQUE automatically?
Show the worked answer
No. CTAS loses it. Recreate the constraint and verify that an actual secondA is rejected.