SQLite not checking integrity of foreign key constraints?

[zaneselvans]

I just attempted to run the ETL on ferc1, eia860 and eia923 including the 2019 data, even though the EIA-to-FERC glue (assigning plants and utilities their PUDL IDs by hand) hasn't been done yet. I expected the datapackages to get generated just fine, but for the loading of those datapackages into SQLite to fail. However, that's not what happened.

• The new FERC respondent_id values (e.g. 519, 529, 531) that were associated with utilities based on the names of plants they own (and some searching through the EIA data and the web) did not show up in the utilities_ferc1.csv within the data package (they also didn't show up in the database, unsurprisingly).
• However, the records associated with these new IDs do appear in the data packages (e.g. in plants_steam_ferc1.csv), and then they do not show up in the database tables after loading.
• The fact that there's a utility_id_ferc1 in plants_steam_ferc1 and not in utilities_ferc1 did not produce an error by violating the foreign key constraint upon loading the data into the database. Instead the records violating the constraint appear to have been silently dropped.

So, are the foreign key constraints ever getting tested? We've been relying on the database ingest to validate this aspect of the database structure.

[zaneselvans]

Holycrap, SQLite does not enforce foreign key constraints by default :facepalm:

However you can turn enforcement on within any given SQLite connection Example here. To make this work in our case we need to use hooks that automatically emit the

PRAGMA foreign_keys=ON;

every time an SQLite3 connection is made, since that's happening down inside of the Datapackage.save() method using an engine that we pass in.

• Docs on SQLite foreign keys from SQLAlchemy
• Docs on SQLAlchemy event registration hooks

I suspect we are going to have a bunch of bad foreign key relationships to chase down now...

[zaneselvans]

The datapkg_to_sqlite script now has a --fkey option which tells it to enforce these relationships and it seems to be functioning well, so I think this issue has been addressed.