OHDSI Home | Forums | Wiki | Github

Caring for orphans

Vocabulary Users
#1

This afternoon, while building a local copy of the concept file, I created Foreign keys linked to the CONCEPT table rather than just create indexes on CONCEPT_ANCESTOR and CONCEPT_RELATIONSHIP.

When I ran my code, I encountered a couple of exceptions, suggesting that there may be orphan values in those tables.

  • Is this sort of thing worth reporting, OR
  • should I assume that OHDSI’s DQ processes will find them soon and there’s no need to point them out, OR
  • am I misunderstanding the CDM?

Gerry

66 IC CREATE FOREIGN KEY (concept_ID_1) REFERENCES CONCEPT ;
ERROR: Creation of foreign key failed because key value “44816479” was not found in data set
V5_LOCAL.CONCEPT.DATA.
67 IC CREATE FOREIGN KEY (concept_ID_2) REFERENCES CONCEPT ;
68 index create relationship_ID ;
69 modify concept_ancestor ;
NOTE: Statements not processed because of errors noted above.
70 IC CREATE FOREIGN KEY (ancestor_concept_ID) REFERENCES CONCEPT ;
ERROR: Creation of foreign key failed because key value “45888206” was not found in data set
V5_LOCAL.CONCEPT.DATA.

#2

Most definitely. We absolutely need the community help keep this all clean.

… there is something wrong with the vocabulary you loaded from Athena. Both 44816479 and 45888206 are in CONCEPT. So, I’d download the latest version.

My hunch is this: 44816479 is a deprecated but valid CPT4. Usually, we don’t do that. In CPT4 we created a temporary exception, because it is a source vocabulary, but also supplies Standard Concepts. In those cases, deprecated Concepts cannot be mapped to anything, creating holes in the data. So, your vocab version is probably from the time when those CPT4 were still invalid. Why it then appeared in CONCEPT_ANCESTOR is beyond me, though.

Doesn’t matter. No need to debug the past. Refresh, and all will be good.

t