Add foreign keys to SQLite dataset

[dracos]

I'm not sure how the data/ifg-ministers-database.sqlite file is being generated, but it’d be great if it could include foreign keys linking the tables. Using sqlite-utils, I added them using the below (probably missed one), but perhaps this can be done as part of your flow that is creating the file:

sqlite-utils add-foreign-key ifg-ministers-database.sqlite appointment person_id person id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite appointment post_id post id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite appointment_characteristics appointment_id appointment id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite organisation_link predecessor_organisation_id organisation id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite organisation_link successor_organisation_id organisation id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite post organisation_id organisation id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite post_relationship post_id post id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite representation person_id person id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite representation constituency_id constituency id
sqlite-utils add-foreign-key ifg-ministers-database.sqlite representation_characteristics representation_id representation id

This will then eg prevent any data with non matching keys from making its way into the database.

[philipnye]

@dracos Much appreciated. Now added (f8793c9) - and I've added this step in to the workflow used to generate the SQLite file