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

TASK
Explain why the "same team playing twice in one gameweek" rule is
enforced in the fixture-entry UI rather than as a database constraint,
and contrast that with the "team can't play itself" rule, which is
enforced directly by CheckConstraint.

SOLUTION

"A team can't play itself" is a fact about a SINGLE row: it only needs
to compare home_team_id against away_team_id on that one fixture, which
is exactly what CheckConstraint("home_team_id != away_team_id") does —
a plain per-row check the database can evaluate every time a row is
inserted or updated, with no need to look at any other row at all.

"A team can't play twice in the same gameweek" is a fact about MULTIPLE
rows at once. To check it, the database would need to look at every
fixture already entered for that gameweek and confirm the new team
isn't already home or away in any of them — a comparison across rows,
not within one row. A plain column-level CheckConstraint has no way to
express "compare this row against every other row with the same
gameweek_id" — that's genuinely outside what a single-row constraint
can check.

Rather than reaching for a more complex database-level mechanism to
express that multi-row rule, this schema leaves it to the application
layer: Chapter 4's fixture-entry UI removes a team from the list of
clickable options once it's already been used somewhere in that
gameweek, so a user is never even able to attempt entering a duplicate
in the first place. The rule is enforced by what the interface allows,
not by the database rejecting an already-submitted request.

WHY THIS WORKS AS AN ANSWER
----------------------------
It correctly identifies the real structural difference between the two
rules — one is a single-row check, the other requires comparing across
multiple rows — explains why a plain CheckConstraint can express the
first but not the second, and states specifically how and where the
second rule actually gets enforced instead.