Read the source and its declared rule
The original target requires a quantity above zero. CHECK(quantity>0) alone does not reject SQL NULL because its expression becomes unknown rather than zero. The corrected schema includes NOT NULL as well as the positive CHECK.
| Record | Input JSON |
|---|---|
| Base input | {"quantity":null} |
Compare the actual paths
The missing input fails with the actual NOT NULL code 1299 and leaves no target row. Zero instead fails CHECK code 275; these are different causes.
| Path | Output JSON |
|---|---|
| Misleading path | {"error":null,"records":[{"quantity":null}]} |
| Corrected path | {"error":{"code":1299},"records":[]} |
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 target(quantity INTEGER '+(corrected?'NOT NULL ':'')+'CHECK(quantity>0));');
const error=failed(()=>db.prepare('INSERT INTO target VALUES(?)').run(i.quantity));
return {error,records:rows(db,'SELECT * FROM target')};
}finally{db.close();}
};
const cases=[{"label":"misleading base","input":{"quantity":null},"expected":{"error":null,"records":[{"quantity":null}]},"corrected":false},{"label":"corrected base","input":{"quantity":null},"expected":{"error":{"code":1299},"records":[]},"corrected":true},{"label":"zero violates positive check","input":{"quantity":0},"expected":{"error":{"code":275},"records":[]},"corrected":true},{"label":"positive quantity accepted","input":{"quantity":2},"expected":{"error":null,"records":[{"quantity":2}]},"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
Zero is rejected and positive 2 is stored. Required presence does not weaken the positive domain.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Zero fails the domain | {"quantity":0} | {"error":{"code":275},"records":[]} |
| Positive two succeeds | {"quantity":2} | {"error":null,"records":[{"quantity":2}]} |
Is CHECK(quantity>0) enough for this required field?
Show the worked answer
No. Add NOT NULL. The missing input then fails and creates no row, while 2 is accepted.