A database schema carries invariants. A primary key identifies a row uniquely. NOT NULL rejects a missing required value. CHECK constrains values within a row, such as a nonnegative balance. A foreign key connects a stored identifier to an existing row. These rules reject invalid states even when an application query is wrong.
SQLite enables foreign-key enforcement per connection; the harness enables it for every new database. It also prevents learner SQL from disabling it. In a real application, enable and verify enforcement when opening each connection, rather than assuming the declaration alone is enough.
Follow the data
A transfer changes two account balances and adds a log row. BEGIN starts one transaction. COMMIT makes the successful transaction durable in a file-backed application; ROLLBACK abandons its changes. Without BEGIN, each successful standalone statement can commit separately, allowing a debit to escape before the log insert fails.
A constraint error in SQLite generally aborts the failing statement, not necessarily every earlier statement in an open transaction. The caller must choose the failure policy. This build models a client that catches a constraint error and rolls back the active transaction. If no transaction was opened, that rollback cannot recover earlier autocommit changes. Hidden fixtures deliberately use a missing endpoint after an earlier update, then inspect the balances and log.
Bind amount, source, destination and transfer_id as values. A WHERE clause belongs on each update. A transfer to the same account debits and credits the same row, so its net balance change is zero. The positive-amount and foreign-key constraints still apply to the log entry.
SAVEPOINT introduces a nested rollback boundary. ROLLBACK TO restores changes made since that point while leaving the savepoint active; RELEASE removes the boundary. It does not turn a released inner savepoint into an independent committed transaction. Teach atomic state changes separately from isolation: this in-memory single-connection exercise cannot prove behavior under competing connections, WAL recovery or production locking.
Work a complete example
The example includes its own schema and data. Every Run creates a new SQLite database, so changing or rerunning it cannot change your other exercises.
CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK(balance >= 0));
CREATE TABLE transfers(id INTEGER PRIMARY KEY, source INTEGER NOT NULL REFERENCES accounts(id), destination INTEGER NOT NULL REFERENCES accounts(id), amount INTEGER NOT NULL CHECK(amount > 0));
INSERT INTO accounts VALUES (1,100),(2,30);
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 1;
UPDATE accounts SET balance = balance + 20 WHERE id = 2;
INSERT INTO transfers(id, source, destination, amount)
VALUES(5, 1, 2, 20);
COMMIT;
SELECT id, balance FROM accounts ORDER BY id;
SELECT id, source, destination, amount FROM transfers ORDER BY id;Diagnose the common failure
A final balance check on a successful sample is not enough to demonstrate atomicity. The failure path is essential. Swallowing a constraint error and committing prior changes violates the transfer contract. Similarly, disabling a constraint merely hides invalid data; it does not repair the query.
Try a change before building
Change the destination to the source account and predict the final balance and log entry. Next try a destination id that does not exist. The standalone snippet reports the error; the graded fixtures additionally model the client's rollback and inspect the resulting database state.
Move into practice
Use constraints and bound writes to preserve an invariant across success and rollback. Checks test the rules; bug hunts ask you to identify a faulty line before seeing its test evidence. The fill-in keeps most of a working statement, and the build asks for the full contract. Public and hidden fixtures vary inputs, preserve duplicate counts and distinguish NULL from text. SQL results appear as tables; predict-output checks use compact JSON rows, one row per line, with null for SQL NULL.
Use the Watch & read resources for the lecture and primary SQLite reference. Reading or watching alone does not grant mastery. These are SQLite exercises; PostgreSQL, MySQL and SQL Server differ in types, syntax, query plans and concurrency behavior.