OHDSI Home | Forums | Wiki | Github

Issue with creating foreign keys in OMOP CDM

Implementers
#1

Hi dear colleagues!

We just started looking into OMOP CDM and I came across an issue. We’re creating the CDM structure with SQL queries. And while executing the queries, I had the following error:

Msg 1778, Level 16, State 0, Line 288
Column ‘.PERSON.person_id’ is not the same data type as referencing column ‘EPISODE.person_id’ in foreign key ‘fpk_EPISODE_person_id’.
Msg 1750, Level 16, State 1, Line 288
Could not create constraint or index. See previous errors.

I looked through the code and I saw that in EPISODE table person_id has a type bigint and in PERSON person_id is of type integer, as in all other tables except for EPISODE. Any reasons for that and how can I fix this problem?

Thank you!

#2

Hello, @jsinkevich and welcome to OHDSI

What code did you use? At the page of CDM documentation is a link to R-package to create CDM instances, may be very helpful. The fields are all int there in all of the sql dialects.

There is no particular reason for different types of fields. In fact, constraints were added to prevent this.

I think the easiest way to fix this is to change the datatype of EPISODE.person_id to int

1 Like
#3

Hi Oleg! Thank you so much for your suggestions!

In fact, at the moment we’re trying to use pure sql, without R. I actually had the same idea on the thing you’ve proposed - just to change bigint to int. But I wonder, would we have any problems with compatibility later on? For example, if we decide to communicate with our partners that also use OMOP CDM, would these code modifications cause compatibility issues in terms of exchanging data?

#4

Should not be any problems with compatibility.

I really hope data partners would use standard OMOP scripts, where all the person_id fields are INT. There is of course a possibility to change any fields according to your data, to bigint, for example, but you need to stay on the same page with your partners.

With the abovementioned scripts it is much easier. Also, you may not use R, but take (or at least take a look at) the SQL inside the R package to not build your own DDLs.

1 Like
#5

Hmm, I just read the 6.0 version description and it was mentioned that all primary keys were converted to bigint.

Changes in v6.0

  • Latitude and Longitude added to LOCATION
  • Contract owner field added to PAYER_PLAN_PERIOD
  • All primary keys were changed to bigint
  • The name of ADMISSION_SOURCE_CONCEPT_ID was changed to ADMITTED_FROM_CONCEPT_ID in VISIT_OCCURRENCE and VISIT_DETAIL
  • All Concept_Ids are now mandatory except for UNIT_CONCEPT_ID, VALUE_AS_CONCEPT_ID, and OPERATOR_CONCEPT_ID. If there is no value available then a Concept_Id should be set to 0 instead of NULL.
  • DEATH table removed and DEATH_DATETIME field added to the PERSON table. Cause of death is stored in the CONDITION_OCCURRENCE
  • DATETIME fields were made mandatory and date fields were made optional.

Would it be a good idea to try to convert all person_id’s to bigint? It might be a nice solution in case we have a really big data pool and the id’s are big.

#6

Oh, yes. That’s why I asked what code did you use.

CDM v6.0 is currently not fully supported by the OHDSI suite of tools and methods.

From the official documentation of CDM v6.0, right above the part you’ve copied:
For new collaborators to OHDSI, please transform your data to CDM v5.4 until such time that the v6 series of the CDM is ready for mainstream use

This is a good catch, to make my answer more precise: change your field format according to the CDM version you are going to use. The current fully supported version of CDM is v 5.4, where all the primary keys are INTs.

1 Like
#7

Ok, I see. Thank you so much, Oleg! I’ll try to do everything in ints :slight_smile:

t