OHDSI Home | Forums | Wiki | Github

Foreign key constraints issue

Implementers
#1

Hi everyone,

I am currently setting up the OMOP CDM database in postgres.

I am focusing on vocabulary only at the moment, and I have added in respective tables, primary keys, constraints and indices.

However, when i try to populate the concept table, it raises issue that

ERROR: insert or update on table “concept” violates foreign key constraint “fpk_concept_domain_id”
DETAIL: Key (domain_id)=(Provider) is not present in table “domain”.

and when i try to populate the domain table , it raises this issue

ERROR: insert or update on table “domain” violates foreign key constraint “fpk_domain_domain_concept_id”
DETAIL: Key (domain_concept_id)=(581456) is not present in table “concept”.

This has become a chicken and egg problem, I am wondering how did you guys solved it?

Only importing the constraints , PKs, and indices after we populate the tables seems like the only way,
I’d like assistance in solving this issue, if there is any way we can solve this, without dropping constraints.

Thanks alot!

#2

OMOP CDM is not a fully normalized model, and by design contains cyclical foreign key references. It does make inserts and uploads tricky.

Usual workflow is to create all constraints after all the data had been uploaded in corresponding tables. I would suggest to drop all constraints and re-run DDL for them after the successful upload.

There are ways around it, declaring constraints as deferred and wrapping the upload in a single transaction, but it is way more trouble than it is worth.

#3

Hi Eduard, thank you for your reply. In that case, I will proceed to drop constraints, import the data then add the constraints in.

However, may I know how did you map your own data to the concept ids ( I heard of USAGI but never ventured into it yet) . Another question: If i were to add in my own local data, then I assume I would run into the cyclical foreign key references again. Do we have to drop constraints again then?

#4

Mapping data to OMOP CDM is a not an easy task to explain. In general, the process would look like this, in order:

  1. Look for existing code columns or external mappings to source mappings to Standard concepts
  2. Full name match and fuzzy match to Standard concepts
  3. Run Usagi
  4. Manual adjustment of mappings of high counts and concepts of interest (e.g. if you want to study vaccines, you include low-count vaccine data here)

As for constraints, dropping and re-adding constraints is usual. Yes, it requires a full table read, but I have never experienced it hanging the server. If you have giant partitioned tables, or just want to do things “clean” way, you can instead once re-create all constraints with ‘deferrable’ property, and start all writing transactions with ‘SET CONSTRAINTS ALL DEFERRED;’.

t