I have seen discussions of people using sqlite. How have people dealt with adding foreign constraints? From my experience, the ALTER TABLE statement in sqlite does not support adding primary keys or foreign key constraints. So this installation SQL does not work:
Considering that SQLite does not enforce foreign key constraints unless explicitly instructed to (docs), it would be safe to just declare the constraints directly in the ddl file; constraints file can then just look like this one string:
PRAGMA foreign_keys = ON;
The problem however is that foreign keys can not even be declared as a part of DDL in SQLite. OMOP CDM is not normalized and contains multiple circular dependencies in foreign key definition (VOCABULARY and CONCEPT table can not function without foreign keys to each other). Such structure is not cleanly supported by any RDBMS.
To make it work in SQLite at all, we would need to either:
- Pick and choose foreign keys to exclude from model definition for practical purposes. Circular dependencies mostly exist in Standardized Vocabularies space, and those tables are not routinely modified by end users. VOCABULARY table rarely participates in JOIN statements, so having a foreign constraint from CONCEPT to it serves limited purpose.
- Enforce “foreign key” relationships using Data Change Notification Callbacks. This would be grossly over-engineered, non-portable and slow; very bad idea for SQLite as it is being used for intents of prototyping solutions in a built-in DBMS.
- Normalize circular dependencies in OMOP CDM on design level by extracting Vocabularies, Concept Classes etc to their own tables; post-pone the fix to SQLite to OMOP CDM v7.0
I am obviously in favor of the first option. SQLite in OMOP is intended for experiments and prototypes, not for hosting production solutions; it should be okay to not have it fully represent all intricacies of OMOP CDM logic.