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

TASK
Explain why a plain UniqueConstraint("fixture_id", "source") wouldn't
work for the Prediction table, and what the
postgresql_where=(Prediction.source != PredictionSource.GUEST) clause
actually changes about which rows the unique rule applies to.

SOLUTION

A plain UniqueConstraint("fixture_id", "source") enforces uniqueness on
every single row in the table, with no way to make an exception for
some of them. That would correctly stop a second "user" prediction, or
a second "expert" prediction, from being stored for the same fixture —
but it would apply exactly as strictly to "guest" predictions too,
meaning the very first guest prediction recorded for a fixture would
already occupy the only (fixture_id, "guest") slot the constraint
allows, and every other guest that week would be rejected as a
duplicate. That directly breaks the actual requirement: multiple real
guests, distinguished only by name, predicting the same fixture in the
same week.

postgresql_where=(Prediction.source != PredictionSource.GUEST) turns
the plain unique constraint into a PARTIAL unique index — one that
PostgreSQL only enforces on rows matching that WHERE condition. In
practice, that means:

- For rows where source is "user", "expert", or "ai" (i.e., the
  condition is true), the (fixture_id, source) pair still has to be
  unique — exactly the "one prediction per source" rule this course
  wants for those three sources.
- For rows where source is "guest" (the condition is false), the index
  simply doesn't apply at all. PostgreSQL never checks uniqueness on
  those rows, so any number of guest predictions can exist for the same
  fixture_id, distinguished from each other only by their own separate
  guest_name value.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains precisely why a plain constraint can't make an exception
for one source (it applies uniformly to every row), then explains
exactly what a partial index changes — enforcement becomes conditional
on the WHERE clause, so three sources stay strictly unique while the
fourth is deliberately left unconstrained by that particular index.