OHDSI Home | Forums | Wiki | Github

Vocab Constraints Issue - Help required

Implementers
, , , ,
#1

Hello Everyone,

I have a question regarding vocabulary updates. so thought I will check with you folks. Request you to help me if you have time. Currently our data is all uploaded and vocab is updated to Jan 2020 version. However during Vocab updates, we have to drop few constraints as it was causing issues while vocab file uploads. Though I followed the order of tables from github for vocab upload, the error still occurs. I guess this issue is discussed in forum as well.

But now when I try to update the constraints based on this https://github.com/OHDSI/DDLgeneratr/blob/master/inst/sql/sql_server/OMOP%20CDM%20constraints.sql, I still see some issues due to missing concepts. Meaning concept is present in concept_relationship table but missing in concept table. So it violates the foreign key constraint. Can anyone help me with this?

Whether I upload data first and define constraints or the other way round, the issue still prevails. Any suggestions on how can I get rid of this error? For example, the error looks like as shown below

“ERROR: insert or update on table “concept_relationship” violates foreign key constraint “fpk_concept_relationship_c_1” DETAIL: Key (concept_id_1)=(2108888) is not present in table “concept”. SQL state: 23503”

Can someone help with this? How do you handle this issue at your site?

Vocab files - Non procedure concepts - Constraints issue
#2

@Christian_Reich @Dymshyts - can I kindly request you to help me with this please?

#3

You selected the cpt4 vocabulary to be a part of the package, right? Have you run the script that retrieves the cpt4 concepts directly from the UMLS server then?

#4

@Alexdavv - In my download history (under Athena), I see that I have downloaded CPT4 concepts as well. I can do two things.

  1. Either retrieve the concepts from UMLS server for CPT4 domain and put it in my concepts table. I found the instructions here

  2. Or ignore the procedure concepts because our data source doesn’t have procedures data. But since it’s not present in concept table and present only in concept_relationship, I guess it would not be easy to filter out these records.

Can help please? Is it possible to remove the CPT4 concepts alone from my vocabulary tables? Is there any filter out/remove these CPT4 related concepts from the vocab tables rather than manually keying in one by one?

#5

Right. All you need is the UMLS account and cpt4.jar tool that in provided in package from Athena.

Once you removed the flag from cpt4 on Athena Download page, it will not be a part of concept, concept_relationship or concept_ancestor tables.

image
image956×218 13.3 KB

#6

Hi @Alexdavv ,

Since I have already downloaded and uploaded the vocab files to my db. Is there any way to remove the concept related to CPT4 domain from the vocab tables?

We know it’s not present in concept table because I didn’t run the jar file. So what I would like to do now is remove all the CPT4 related concepts from my vocab tables like concept_relationship, concept_synonym, relationshop etc.

Is it possible?

I wish to do the above because if I have to do the UMLS approach then I might have to clear my vocab tables and reload them again which I don’t wish to.

#7

You can do, but you need to upload the file CONCEPT_CPT4.csv from your Athena package to your server.

Then run:

DELETE FROM concept_relationship
WHERE concept_id_1 IN (SELECT concept_id FROM concept_cpt4 WHERE concept_id IS NOT NULL)
OR concept_id_2 IN (SELECT concept_id FROM concept_cpt4 WHERE concept_id IS NOT NULL);

DELETE FROM concept_ancestor
WHERE ancestor_concept_id IN (SELECT concept_id FROM concept_cpt4 WHERE concept_id IS NOT NULL)
OR descendant_concept_id IN (SELECT concept_id FROM concept_cpt4 WHERE concept_id IS NOT NULL)

DELETE FROM concept_synonym
WHERE concept_id IN (SELECT concept_id FROM concept_cpt4 WHERE concept_id IS NOT NULL)

Othervise, you can drop all the records having concept_id not mentioned in the concept table, but then I’d not be sure about the data consistency.

t