Premier League Predictor: Astro — Chapter 5, Exercise 1
====================================================

TASK
Explain why a plain UNIQUE (fixture_id, source) index wouldn't work
for the predictions table, what the WHERE source != 'guest' clause
actually changes about which rows the unique rule applies to, and
correct the common assumption that a conditional partial index like
this is a PostgreSQL-only feature.

SOLUTION

A plain UNIQUE (fixture_id, source) index would treat every source
identically: it would allow at most one row per (fixture_id, source)
pair across the whole table, with no exceptions. That's exactly the
right rule for the "user", "expert", and "ai" sources, which really
should only ever predict once per fixture — but it would also block a
second "guest" row for the same fixture, even though the whole point
of the guest source is that several different, separately-named
guests can each predict the same fixture in the same week.

The WHERE source != 'guest' clause on the index changes which rows
the uniqueness rule is actually checked against. SQLite's own partial
index feature only includes rows in the index that satisfy the
condition, and the UNIQUE guarantee is enforced only across the rows
that are actually in the index — so "guest" rows are simply never
compared against each other for uniqueness at all, while "user",
"expert", and "ai" rows are compared exactly as a plain unique index
would compare them. The result is a rule that reads as "at most one
row per fixture for every source except guest," applied by the
database itself rather than trusted to application code.

Correction: a partial index conditioned on an arbitrary expression is
not a PostgreSQL-only feature. Checked directly against SQLite's own
official documentation, partial indexes have been fully supported
since SQLite 3.8.0, released in 2013 — well over a decade before this
course was written — using syntax that's close to a direct port of
the PostgreSQL version: CREATE UNIQUE INDEX name ON table(cols) WHERE
expr. The idea that this capability belongs specifically to
PostgreSQL and not to SQLite doesn't hold up once actually checked.

WHY THIS WORKS AS AN ANSWER
----------------------------
It explains precisely what problem a plain unique index would create
(blocking legitimate multiple guest rows), correctly describes the
real mechanism a partial index uses to solve it (only indexing, and
therefore only checking, the rows matching the WHERE condition), and
explicitly corrects the PostgreSQL-only assumption with a real,
verifiable version number and date rather than just asserting SQLite
"can do it too."