SQL Server invalid metadata to concept constraint

[cg3y]

The following line:

ALTER TABLE metadata ADD CONSTRAINT fpk_metadata_concept FOREIGN KEY (metadata_concept_id) REFERENCES concept (concept_id);

in the file:

SQL Server\OMOP CDM sql server constraints.txt

is not valid and causes the script to fail.

The problem is that the CDM Version is installed by the scripts into the metadata table with a concept id of 0. There is no concept_id of 0 in the concept table, and there is no vocabulary that supports a concept_id of 0.

A similar problem exists with the following line:

ALTER TABLE metadata ADD CONSTRAINT fpk_metadata_type_concept FOREIGN KEY (metadata_type_concept_id) REFERENCES concept (concept_id);

i.e. there is no entry in the concept table with a metadata_type_concept_id of 0 which is the value of the CDM Version record.

[clairblacketer]

Hi there is a concept_id of 0 in the concept table. https://athena.ohdsi.org/search-terms/terms/0

It is necessary to indicate source values that do not have a mapping so it should be in your concept table.

[cg3y]

I see. It didn't show up until I loaded the vocabularies. I was following the readme instructions literally which said:

Note: you could also apply the constraints and/or the indexes before loading the data, but this will slow down the insertion of the data considerably.

Looks like that needs to be altered to indicate that the vocabulary data must be loaded first before applying the constraints.

Thanks!