Tables, data types, and constraints

Make rules visible in the schema

An application check can be bypassed by another writer. Put rules the database can enforce into constraints. A primary key identifies a row; a foreign key checks a reference; a unique constraint rejects duplicate keys; a check constraint limits the values a row can contain. NOT NULL is separate: a check that evaluates to unknown does not reject a null. Constraint behavior↗.

Use integer cents for this single-currency fixture. For values that need an exact decimal scale, evaluate numeric; binary floating point is unsuitable when exact decimal arithmetic is required. Specify units and bounds. We use timestamptz for instants: display depends on the session time zone, and the original named zone is not retained. Numeric types↗, date/time types↗.

Create the shared fixture once

Start the container from the previous bite. Save this as schema.sql in your lab directory. It intentionally fails if the schema already exists so it cannot silently replace your work.

BEGIN;
CREATE SCHEMA study;
CREATE TABLE study.accounts (
  account_id bigint PRIMARY KEY,
  label text NOT NULL UNIQUE
);
CREATE TABLE study.entries (
  entry_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES study.accounts(account_id),
  request_key text NOT NULL,
  amount_cents bigint NOT NULL CHECK (amount_cents <> 0),
  created_at timestamptz NOT NULL,
  note text,
  UNIQUE (account_id, request_key)
);
INSERT INTO study.accounts VALUES (1, 'demo-a'), (2, 'demo-b'), (3, 'demo-empty');
INSERT INTO study.entries (account_id, request_key, amount_cents, created_at, note)
VALUES
  (1, 'r-1', 1000, '2026-09-01T09:00:00Z', 'opening fixture'),
  (1, 'r-2', -250, '2026-09-01T09:05:00Z', NULL),
  (2, 'r-3', 2000, '2026-09-01T10:00:00Z', NULL);
COMMIT;

docker exec -i learnwithsk-postgres psql -X -v ON_ERROR_STOP=1 \
  -U postgres -d study < schema.sql
docker exec learnwithsk-postgres psql -X -U postgres -d study \
  -c '\d study.entries'

The fixture has three accounts and three entries. Its signed entries are a teaching device, not double-entry accounting. It permits a negative total and has no currency conversion or authorization model. No real customer information belongs here.

The account label lives in one table, with references from entries. This lets you update the label in one place without leaving inconsistent copies on individual entries. Normalization helps represent dependencies; it does not mean every field must become its own table.

Test one rejected write

In interactive psql, deliberately try a zero amount, then roll back:

BEGIN;
INSERT INTO study.entries (account_id, request_key, amount_cents, created_at)
VALUES (1, 'invalid-zero', 0, now());
ROLLBACK;

Expect a check-constraint error and an unchanged entry count. In separate transactions, predict the results for an unknown account, a null request key, and a repeated (account_id, request_key). Roll back each trial. Sequence values can have gaps after failed or rolled-back inserts; IDs are not row counts.

Checkpoint

Explain which rule prevents each invalid row. A uniqueness rule is only part of an idempotency contract: the application must also return the prior result and detect a different payload sent under the same key. Do not add these fixture constraints to MicroBank without checking its real schema and migration plan.

Check the rejected rows

An unknown account violates the foreign key. A null request key violates NOT NULL. Repeating account 1 plus r-1 violates the composite unique constraint, even with a different amount. A zero amount violates the check. After each deliberate error, rollback ends the failed transaction; otherwise subsequent statements in that transaction remain blocked by its failed state.

Notice a limit: the same request key on a different account is permitted by this fixture's composite rule. The database also does not automatically return the earlier operation to a retrying API caller. State that application contract separately. Next, read the valid rows with SQL before interpreting their totals.