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

TASK
Demonstrate the real consequence of forgetting PRAGMA foreign_keys =
ON: insert a fixture row referencing a team ID that doesn't exist,
once against a database connection where the pragma was never set,
and once against one where it was — then write down exactly what
happens in each case.

SOLUTION

A small standalone script, deliberately separate from the app's own
db.ts, so the pragma can be toggled on purpose:

  import Database from 'better-sqlite3';

  const db = new Database(':memory:');
  db.exec(`
    CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE fixtures (
      id INTEGER PRIMARY KEY,
      home_team_id INTEGER REFERENCES teams(id),
      away_team_id INTEGER REFERENCES teams(id)
    );
    INSERT INTO teams (id, name) VALUES (1, 'Arsenal');
  `);

  // --- Case 1: pragma never set ---
  db.prepare(
    'INSERT INTO fixtures (home_team_id, away_team_id) VALUES (1, 999)'
  ).run();
  console.log('Case 1 (no pragma): insert completed with no error.');
  console.log(db.prepare('SELECT * FROM fixtures').all());

  // --- Case 2: pragma set on a fresh connection ---
  const db2 = new Database(':memory:');
  db2.pragma('foreign_keys = ON');
  db2.exec(`
    CREATE TABLE teams (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE fixtures (
      id INTEGER PRIMARY KEY,
      home_team_id INTEGER REFERENCES teams(id),
      away_team_id INTEGER REFERENCES teams(id)
    );
    INSERT INTO teams (id, name) VALUES (1, 'Arsenal');
  `);
  try {
    db2.prepare(
      'INSERT INTO fixtures (home_team_id, away_team_id) VALUES (1, 999)'
    ).run();
  } catch (err) {
    console.log('Case 2 (pragma on):', (err as Error).message);
  }

What actually happens in each case:

  Case 1 (no pragma): the INSERT succeeds without any error at all,
  even though team ID 999 has never existed in the teams table. A
  fixture row now exists in the database pointing at a team that
  isn't there — a real, silent data-integrity bug that would only
  surface later, the first time something tries to join fixtures
  against teams and finds nothing on the other end.

  Case 2 (pragma on): the exact same INSERT throws a real error —
  "FOREIGN KEY constraint failed" — and the row is never written at
  all. The broken reference is caught immediately, at the moment it
  would have been created, rather than silently stored.

WHY THIS WORKS AS AN ANSWER
----------------------------
It runs the identical insert twice against two differently configured
connections, shows the schema and pragma calls in full so the setup
is unambiguous, and states precisely what happens in each case — a
silent bad row in one, a genuine thrown error in the other — matching
exactly what the chapter's own warn-box describes rather than just
repeating the claim without demonstrating it.