Transactions, constraints, and query plans

The idea

SQLite transactions group changes into a unit that can be committed or rolled back. This matters when several statements jointly maintain an invariant, such as transferring a quantity between records. Application code must still detect failure and choose rollback; merely issuing BEGIN does not prove a business rule was preserved. Constraints provide another line of defense for invariants that the database can express.

An index can speed selective lookups and some ordering operations, but it consumes storage and adds maintenance work to writes. Choose it from observed query patterns and inspect EXPLAIN QUERY PLAN rather than assuming every index helps. Plan text and optimizer choices can change, so test query results independently of plan formatting. Transaction and schema examples belong in a SQLite environment that permits writes; they are separate from read-only exercises.

CREATE TEMP TABLE balances(id INTEGER PRIMARY KEY, amount INTEGER NOT NULL CHECK(amount >= 0));
INSERT INTO balances VALUES(1,10),(2,0);
BEGIN;
UPDATE balances SET amount=amount-3 WHERE id=1;
UPDATE balances SET amount=amount+3 WHERE id=2;
COMMIT;
SELECT id,amount FROM balances ORDER BY id;

id | amount
1  | 7
2  | 3

Both updates commit as one unit and preserve the total of ten. The CHECK constraint separately rejects a negative stored amount.