It appears that there are some discrepancies between CDM v5 specs and what is actually distributed in SQL Server vocabulary tables based on DDL.
The field “subject_concept_id” in the table “COHORT_DEFINITION” is listed in the CDM specs as a foreign key however after running DDL script this field ends up as a regular field SQL server.
The field “source_concept_id” in the table “SOURCE_CONCEPT_MAP” is listed in the CDM specs as a foreign key however after running DDL script this field ends up as a regular field in SQL server.
Also, in the COHORT table (which is not part of Standardized Vocabularies) there are several discrepancies in number of fields and their definition between CDM v5 specs and actual table created in SQL server after DDL script is executed.
subject_concept_id – this is a foreign key, but from a relational database perspective a little funny. Beause it is a FK to either the person or the provider table. Reason is: We could build provider cohorts and do studies on those. That use case hasn’t actually materialized, yet. Let’s see.
source_concept_id in SOURCE_CONCEPT_MAP? Not sure that even exists? There is a SOURCE_TO_CONCEPT_MAP table, and it contains a source_code, but that one is definitely not a FK. What are you looking at?
COHORT table: Can you show me where you get the DDL from?