Missing foreign key constraints

[bruth]

The following foreign key constraints are not in the DDL files:

• concept_synonym.language_concept_id
• source_to_concept_map.source_concept_id
• cohort_definition.subject_concept_id
• attribute_definition.attribute_type_concept_id

[cgreich]

Thanks, @bruth. Will check it out.

[clairblacketer]

@cgreich @bruth was this ever fixed?

[bruth]

Hi @clairblacketer. My organization maintains a repo of data models here which includes OMOP. That repository is consumed by two services we host, one that renders the data models as browsable web pages and one that provides SQL DDL generation. This is all dynamic and driven by the files in the repository we maintain.

[bruth]

And to answer your question.. I am not sure if it was updated in this repo, but once we setup the data models repo, we have been updating and using that as the source of code generation.

[cgreich]

@bruth:

This is nice. We should adopt this. How do you actually do it behind the scenes?

[bruth]

@burrowse Can you describe the workflow for updating versions? @aaron0browne Can you describe what has been done?

/cc @murphyke

[burrowse]

@bruth As far as updating versions, we maintain CSV files for each CDM release. These files are broken down into:

1. Constraints - CSVs files defining all not nulls and primary keys constraints for each table in the version.
2. Definitions - CSVs files with the OMOP definition of all columns in the version, along with reference information for the tables (e.g. _concept_ids)
3. Indexes - CSV defining all indexes for all tables.
4. References - CSV files defining all references/foreign key constraints.
   5)Schema - CSVs with the data type for each column in the version.

We update the CSVs for new columns or changes to columns etc so it still requires some human intervention. The service itself then validates the referential integrity. The DDL is then generated from these files.

@aaron0browne will be able to speak to the more technical piece (sql alchemy etc) of how that is done.

I apologize for the delay!

[clairblacketer]

closing this as the initial issue has been fixed