dev_tables.json: 2 foreign keys missing for european_football_2 (Match.league_id → League.id, Match.country_id → Country.id)

[tejusarora]

Summary

In the BIRD dev set (dev_20240627), dev_tables.json omits two foreign keys for the european_football_2 database that are declared in the shipped SQLite database itself:

• Match.league_id → League.id
• Match.country_id → Country.id

These are the only two FK annotations missing across all 11 dev databases (verified by comparing PRAGMA foreign_key_list output against dev_tables.json for every table in every dev database).

Evidence

dev_tables.json declares 24 FKs on Match (all 22 player-slot columns plus both team columns), but not league_id/country_id.

The SQLite database declares them (PRAGMA foreign_key_list("Match")):

League   league_id   -> None   (implicit: references League's PK, i.e. League.id)
Country  country_id  -> None   (implicit: references Country's PK, i.e. Country.id)

Likely root cause

Both missing FKs are the implicit-PK form (REFERENCES League with no column list), which PRAGMA foreign_key_list reports with a NULL "to" column. The other 24 FKs on Match use the explicit form and were extracted correctly. It looks like the schema-extraction script that produced dev_tables.json skips (or fails to resolve) FK rows whose referenced column is NULL instead of resolving them to the parent table's primary key.

Impact

• 13 dev gold queries join on Match.league_id = League.id (question_ids 1025, 1028, 1030, 1038, 1042, 1049, 1073, 1091, 1092, 1139, 1142, 1143, 1145).
• Systems that use dev_tables.json foreign keys for schema linking / join-path pruning are denied exactly the edge those gold answers require, which can depress scores on these 13 questions for reasons unrelated to model quality.

Reproduction

import json, sqlite3

t = next(d for d in json.load(open("dev_tables.json")) if d["db_id"] == "european_football_2")
cols, tbls = t["column_names_original"], t["table_names_original"]
json_fks = {(tbls[cols[a][0]], cols[a][1], tbls[cols[b][0]], cols[b][1])
            for a, b in t["foreign_keys"]}
print(any(f[1].lower() == "league_id" for f in json_fks))   # False

con = sqlite3.connect("dev_databases/european_football_2/european_football_2.sqlite")
print([r for r in con.execute('PRAGMA foreign_key_list("Match")') if r[3] in ("league_id", "country_id")])
# both FKs present, with to-column None (implicit PK reference)

Suggested fix

When extracting FKs, resolve PRAGMA foreign_key_list rows with a NULL referenced column to the parent table's primary key. Happy to submit a PR for the two missing entries and/or the extraction fix if useful.

---

Found via an automated semantic audit (static join-safety analysis of the dev gold queries against the declared schema, then verified by hand against the SQLite databases).