1. Model related facts and enforce invariants
By the end
- Separate members, equipment and loan events into related tables.
- Use primary keys, foreign keys and checks for distinct kinds of rules.
A repeated name is a poor join key: people can share names or change them. Give each member and item a stable integer id. The loans table refers to these ids rather than repeating names and titles on every event. A primary key identifies one row; a foreign key links that row to an existing related row.
Rules should also live in the database. Our available stock must be a non-negative integer; loan quantity must be a positive integer. SQLite’s flexible typing makes the typeof checks useful here: declaring INTEGER alone does not express that whole-number contract for every inserted value. NOT NULL separately rejects missing quantities.
Enable foreign-key enforcement on this connection before writing data, and check that it is on. The example starts a fresh :memory: database each run. available describes equipment on the shelf now; the seeded loans describe separate lending records, including one already returned record. They are not all current loans.
We choose Python 3.12+ autocommit=True so each standalone statement finishes outside an explicit SQL transaction. Later we will use SQL BEGIN, COMMIT and ROLLBACK deliberately. Do not assume the connection’s Python commit() method controls that explicit-autocommit example.
Worked example
Create three related tables and load three members, three items and three loan records into a fresh in-memory database.
- Open :memory:, enable foreign keys and verify the pragma returns 1.
- Create member/item primary keys and loan foreign keys. Keep available and quantity checks separate.
- Insert the original fixture using parameters; one due_day is None, meaning no known due day.
- Read items with ORDER BY id so the expected row order is explicit.
import sqlite3
# Python 3.12+. All practice data lives in this process, not an existing file.
con = sqlite3.connect(":memory:", autocommit=True)
con.execute("PRAGMA foreign_keys = ON")
assert con.execute("PRAGMA foreign_keys").fetchone()[0] == 1
con.executescript("""
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL CHECK(length(trim(name)) > 0)
);
CREATE TABLE items (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL,
available INTEGER NOT NULL
CHECK(typeof(available) = 'integer' AND available >= 0)
);
CREATE TABLE loans (
id INTEGER PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members(id),
item_id INTEGER NOT NULL REFERENCES items(id),
quantity INTEGER NOT NULL
CHECK(typeof(quantity) = 'integer' AND quantity > 0),
returned INTEGER NOT NULL DEFAULT 0 CHECK(returned IN (0, 1)),
due_day TEXT
);
""")
con.executemany("INSERT INTO members VALUES (?, ?)",
[(1, "Ada"), (2, "Ece O'Neil"), (3, "Sam")])
con.executemany("INSERT INTO items VALUES (?, ?, ?)",
[(101, "Tripod", 3), (102, "Lamp", 2), (103, "Microphone", 0)])
con.executemany("INSERT INTO loans VALUES (?, ?, ?, ?, ?, ?)", [
(1, 1, 101, 2, 0, None),
(2, 2, 101, 1, 1, "2026-10-10"),
(3, 1, 102, 1, 0, "2026-10-20"),
])
print(con.execute("SELECT id, title, available FROM items ORDER BY id").fetchall())Items are (101, Tripod, 3), (102, Lamp, 2), (103, Microphone, 0). Zero availability is valid; a negative or fractional stock is not.
Try it yourself
After setup, try inserting one loan for member 999, then try an item with available=-1. Catch sqlite3.IntegrityError for each and confirm the row counts remain 3 members, 3 items and 3 loans.
- Missing member is rejected by the foreign key, not silently created.
- Negative stock fails its check and does not leave a partial row.
Reveal the practice solution
Use INSERT INTO loans(member_id,item_id,quantity) VALUES (999,101,1), then INSERT INTO items VALUES (104,'Cable',-1), each inside its own try/except. Both fail. COUNT(*) on each table stays 3. Errors explain different broken invariants; do not convert them into success.
Watch for this mistake: A declared relationship is not enough if foreign-key enforcement is off. Check the actual connection, and do not use a display name as the identity of a person.
Sources and review date
- Python: sqlite3
Parameter binding and explicit SQL transaction control using Python 3.12+ autocommit=True. Checked: .
- SQLite: CREATE TABLE
NOT NULL, CHECK and key constraints. Checked: .
- SQLite: Foreign Key Support
Connection-level foreign key enforcement and invalid parent references. Checked: .
Explanations, examples and quiz questions are original KitForma material. These links support technical facts and curriculum alignment.