Football Schema for SQLite seems to miss any Foreign Keys?

[MarcusRiemer]

I ran the following commands to obtain a SQLite database for the Bundesliga:

gem install sportdb
# I couldn't run sportdb new de without the following extra dependencies
gem install sportdb-langs sportdb-service
sportdb new de

I then uploaded the resulting SQLite file to my research project, which has a database visualisation component. The resulting visual schema doesn't look like it detected any foreign keys (image behind link).

I then briefly checked the leagues table and it doesn't seem to specify any foreign key.

CREATE TABLE "leagues" (
    "id"    integer NOT NULL PRIMARY KEY AUTOINCREMENT,
    "key"   varchar NOT NULL,
    "title" varchar NOT NULL,
    "country_id"    integer,
    "club"  boolean NOT NULL DEFAULT 0,
    "created_at"    datetime(6) NOT NULL,
    "updated_at"    datetime(6) NOT NULL
);

[geraldb]

Thanks for reporting the missing foreign key constraints. In ActiveRecord-generated (SQL) schemas foreign keys use the table name + id convention, thus, in the example above country_id is the foreign key to the country table / relation. All primary keys are always called id.

From the reddit thread: Enforced foreign key constraints are missing for historic reasons and yes, more need to get added (starting with Rails 6+ there's now support for foreign key constraints for SQLite in ActiveRecord). I'm happy to update the schema, see https://github.com/sportdb/sport.db/blob/master/sportdb-models/lib/sportdb/schema.rb but unfortunately this will take some weeks (as this also requires an upgrade of minimum required ruby version and more).

[MarcusRiemer]

Yes, I suspected that adding the actual migrations for the foreign keys would be the smaller part of the work. Feel free to come back at me once the groundwork is done, in general I would be thrilled to provide these datasets as a possible learning resource for teachers and children.