Wiki

To better align the model field names to common primary and foreign field naming conventions in the SemMedDb portion of GBK (i.e. ‘*_id’ format), the Implicitome database primary and foreign keys were renamed across all of the tables to include an underscore character before the ‘id’ suffix. Certain other fields were renamed either for Django compatibility. Here are the changes, along with the MySQL SQL used to make the adjustments:

concept table: the concept_id field also needs to be ‘autoincremented’ in the concept table itself:

#!SQL

ALTER TABLE concept DROP PRIMARY KEY;
ALTER TABLE concept CHANGE COLUMN conceptid concept_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

dblink table:

#!SQL

ALTER TABLE dblink DROP PRIMARY KEY ; 
ALTER TABLE dblink  CHANGE COLUMN dblinkid dblink_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST,
                    CHANGE COLUMN conceptid concept_id INT NOT NULL,
                    CHANGE COLUMN dbid db_id VARCHAR(4) NOT NULL,
                    CHANGE COLUMN id identifier VARCHAR (255);

term table: in this table, the existing primary keys were dropped. The fields ‘termid’ renamed to ‘subterm_id’ and ‘conceptid’ renamed to ‘concept_id’, then a new autoincrement primary key (‘term_id’) added.

#!SQL

ALTER TABLE term DROP PRIMARY KEY; 
ALTER TABLE term CHANGE COLUMN conceptid concept_id INT NOT NULL,
                 CHANGE COLUMN termid subterm_id INT NOT NULL,
                 ADD UNIQUE (concept_id, subterm_id);
ALTER TABLE term ADD term_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

* relation table: concept1_id and concept2_id ought to be foreign keys…but…

#!SQL

ALTER TABLE relation DROP PRIMARY KEY, 
ALTER TABLE relation CHANGE COLUMN relationid relation_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE relation CHANGE COLUMN conceptid1 concept1_id int NOT NULL,
                     CHANGE COLUMN conceptid2 concept2_id int NOT NULL,
                     ADD UNIQUE (concept1_id, concept2_id),
                     CHANGE COLUMN relationtypeid relationtype_id INT UNSIGNED NOT NULL;

tuples table: all id’s already seem to be in the ‘*_id’ format