Fix DB constraint violations

[zaneselvans]

Description

In getting the SQLite/Parquet ETL (#1176) working, some previously enforced database constraints were relaxed. There are also new constraints that we want to impose on the structure of the DB to keep it tidy, and enable programmatic use of the relational structure, most immediately in the context of the entity harvesting & resolution process (#639). These issues include modifying our data processing to ensure that no primary keys contain null values, and all are unique, making sure that foreign key relationships and data types are being checked by the database, and that appropriate NA/Null values are being used in the DB.

This work should be billed to the Sloan Metadata/Harvest Revamp project in Harvest.

Motivation

• Our primary data products are well-normalized relational databases. These constraint checks are part of how we know that the data is clean, and the structure allows much more automated use of different tables in conjunction with each other, both by us and others.
• Some of these constraint violations highlight existing data normalization problems that need to be addressed in order for the data to make sense in many analyses.
• Many (though maybe not all?) of these issues need to be addressed before the new entity harvesting & resolution process (Refactor Harvesting [Sloan] #639) can be fully implemented. That refactor is blocking a lot of new data integration. We should identify which issues are truly blocking.

In Scope

• All of the appropriate natural primary keys identified in Review resources with no primary key #846 are actually being used as primary keys in the database.
• Debug foreign key constraints in direct-to-sqlite ETL #1196
• Eliminate null values in ownership_eia860 primary key fields #1207
• Eliminate null values in generation_eia923 primary key fields #1208
• Address 541 utilities that are missing from 2010 EIA 860 data #1262
• ownership table reporting owner in operator column in 2010. #1116
• Foreign key constraint violations between plants_small_ferc1 and plants_ferc1 #1263
• Integer primary keys are not automatically nullable=False #1194
• Implement CHECK(type) constraints in SQLite ETL/schema #1197
• Modify generation_fuel_eia923 transform to yield natural primary key #851
• Modify boiler_fuel_eia923 transform to yield natural primary key #852
• Ensure categorical columns use appropriate NA values #1210

Out of Scope

• Enrich table and column level metadata #426 Adding new constraints that we don't already have codified.