Catch a Broken Foreign-Key Boundary

A configured connection should refuse a loan whose item or member does not exist. Seeing PRAGMA foreign_keys report a value is weaker evidence than attempting the forbidden write and observing both the exception and the unchanged database.

Edit tests/test_catalog.py and complete test_foreign_keys_reject_missing_parents(tmp_path). Start with create_fresh_database(tmp_path, "foreign-keys.db"), then open it through catalog_db.open_database(database_path). That public connection factory is the boundary under test.

Arrange one real item and one real member with bound INSERT calls, then commit that known starting state. Attempt a loan with a missing item ID while the member ID is valid. Use pytest.raises(sqlite3.IntegrityError) around the single execute() call, then roll back so the connection is ready for the next attempt. Repeat with a valid item ID and a missing member ID. Both failures should be the database’s sqlite3.IntegrityError, not a duplicate-primary-key error chosen by the test.

Close the acting connection. Open the same path with a new connection and select the loan IDs. Assert that no loan row exists. This last observation answers a different question from the exception: the failure happened, and it left no durable partial state.

It may be tempting to assert only that foreign-key enforcement is turned on. That checks a setting, but it does not prove the promised boundary works with real parent references. Likewise, querying through the original connection alone does not prove what another connection will see.

Press Run. The fixed entry calls pytest.main(["-q", "-p", "no:cacheprovider", "tests/test_catalog.py"]) and should report:

Database tests passed: 3

Submit runs the accumulated tests twice and then checks this story against a connection factory that no longer enables foreign keys. If the new test expects the exact exception and reopens to inspect loans, it will catch that behavior change. The other two tests remain independent because every function receives its own tmp_path and chooses its own filename.