Premier League Predictor: Astro — Chapter 2, Exercise 2
====================================================

TASK
Explain why fixtures' home_team_id and away_team_id columns don't
need anything like SQLAlchemy's foreign_keys=[...] argument in this
raw-SQL, better-sqlite3 schema, even though both columns reference
the same teams table.

SOLUTION

The FastAPI & PostgreSQL sibling course uses SQLAlchemy, an ORM that
builds Python-level "relationship" attributes (home_team, away_team)
by inferring which foreign key column each one should follow. When
two foreign keys on the same model both point at the same target
table, SQLAlchemy has two equally plausible foreign keys to choose
from for each relationship, and can't work out on its own which one
belongs to which — that ambiguity is exactly what foreign_keys=[...]
exists to resolve, by telling SQLAlchemy explicitly which column each
relationship should use.

This course's own schema has no such inference step anywhere in it.
home_team_id and away_team_id are just two plain integer columns,
each with its own explicit "REFERENCES teams(id)" clause written
directly in the CREATE TABLE statement. When a query reads a fixture
row back with better-sqlite3, it comes back as a plain object with
both columns already present by name — there's no separate
relationship object being built, and therefore nothing that could
ever need to guess which foreign key belongs to which relationship.

In short: foreign_keys=[...] fixes an ambiguity created specifically
by an ORM's own relationship-inference layer. Raw SQL with a direct
database driver has no such layer, so the ambiguity it exists to
resolve never actually arises in the first place.

WHY THIS WORKS AS AN ANSWER
----------------------------
It correctly explains what foreign_keys=[...] actually does and why
it's needed in an ORM context, then explains precisely why this
stack's own lack of a relationship-inference layer means the same
class of problem simply has nothing to attach to here — not just
asserting "SQLite doesn't need it," but explaining the real mechanism
behind why.