Premier League Predictor: FastAPI & PostgreSQL — Chapter 3, Exercise 3
====================================================

TASK
Explain why db.flush() is used instead of db.commit() when creating a
brand-new Team inside add_team_to_season, and what real guarantee would
be lost if commit() were used at that point instead.

SOLUTION

The new SeasonTeam row needs team.id to exist before it can be created
— team_id is a required foreign key column on SeasonTeam. When a brand
new Team is being created in the same request, that id doesn't exist
yet until the INSERT actually runs against the database. flush() sends
that pending INSERT to PostgreSQL immediately and lets the database
assign the id, without ending the surrounding transaction — so
team.id becomes available to use in the very next line, while both the
Team insert and the SeasonTeam insert that follows are still part of
one single, not-yet-finished transaction.

If commit() were used instead of flush() at that point, the Team
insert would be permanently saved to the database on its own, as a
fully separate transaction from the SeasonTeam insert that comes after
it. That breaks the real guarantee this route is built to provide:
that a new Team and its matching SeasonTeam row either both succeed
together or both fail together. If something went wrong after an early
commit() — for example, the season turned out to already have 20 teams
and the request was rejected, or the database connection dropped before
the SeasonTeam insert ran — the new Team row would already be
permanently saved with no SeasonTeam to go with it: a real, orphaned
row left behind by a request that otherwise failed.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains the real, concrete reason flush() is needed (team.id must
exist before the SeasonTeam row can reference it) and distinguishes
it clearly from commit() (flush keeps the transaction open, commit
ends it permanently), then states the specific real guarantee — atomic
success-or-failure across both rows — that using commit() too early
would break, with a concrete example of the orphaned-row outcome.