Foreign Key constraints for same columns on the same table

Support - Assistance technique
#1

Dear Dev,

We have noticed on numerous DHIS2 databases that there sometimes are multiple Foreign Key constraints on the same table for the same column. On Sierra Leone some examples:

categories_categoryoptions
FOREIGN KEY
fk_categories_categoryoptions_categoryid
categoryid
categories_categoryoptions
FOREIGN KEY
fkf453b3bdd24076b3
categoryid

categorycombos_categories
FOREIGN KEY
fk731150b827f29ff
categorycomboid
categorycombos_categories
FOREIGN KEY
fk_categorycombos_categories_categorycomboid
categorycomboid

indicatorgroupmembers
FOREIGN KEY
fkf01275498be51834
indicatorgroupid
indicatorgroupmembers
FOREIGN KEY
fk_indicatorgroupmembers_indicatorgroupid
indicatorgroupid

organisationunitattributevalues
FOREIGN KEY
fk601dda02b8e4fd1
attributevalueid
organisationunitattributevalues
FOREIGN KEY
fkcoo6svgtx8pre5fabnjuyhgpf
attributevalueid
organisationunitattributevalues
FOREIGN KEY
fk601dda0fb4caaad
organisationunitid
organisationunitattributevalues
FOREIGN KEY
fk7utgogpv8n5r4yxm41lhd70i0
organisationunitid

orgunitgroupmembers
FOREIGN KEY
fk55fa7022b8b57b9d
orgunitgroupid
orgunitgroupmembers
FOREIGN KEY
fk_orgunitgroupmembers_orgunitgroupid
orgunitgroupid

programstageinstancecomments
FOREIGN KEY
fk5323f083fc199ca1
trackedentitycommentid
programstageinstancecomments
FOREIGN KEY
fkki1hrag24gcjuq78oafhuk1wh
trackedentitycommentid
programstageinstancecomments
FOREIGN KEY
fk_programstageinstancecomments_trackedentitycommentid
trackedentitycommentid

reporttable_organisationunits
FOREIGN KEY
fk4b7becf027251155
reporttableid
reporttable_organisationunits
FOREIGN KEY
fk4b7becf03aab0439
reporttableid
reporttable_organisationunits
FOREIGN KEY
fk4b7becf0cc2046c1
reporttableid

Are these “duplicates” a matter of concern and should this be maintained regularly by removing them? Do these “duplicates” add to the database overheads/performance degradation?

Best Regards

This message and any attachments are subject to a disclaimer published at http://www.hisp.org/policies.html#comms_disclaimer. Please read the disclaimer before opening any attachment or taking any other action in terms of this electronic transmission. If you cannot access the disclaimer, kindly send an email to disclaimer@hisp.org and a copy will be provided to you. By replying to this e-mail or opening any attachment you agree to be bound by the provisions of the disclaimer.

···

Ant Snyman

Cell: 0824910449

Landline: 028 2713242

Health Information Systems Program - SA

#2

Hi,

When extracting all “FOREGIN KEY” and “UNIQUE” constraints from a new 2.28 database created from scratch (i.e. create empty database and start up DHIS2 2.28 pointing to it), you get

  • 918 “FOREIGN KEY” constraints

  • 420 “UNIQUE” constraints

When doing the same on the 2.28 Play SL demo right now, I got:

  • 1,215 “FOREIGN KEY” constraints (the extra ~300 are probably mostly duplicates)

and more interestingly only 338 “UNIQUE” constraints. So 82 unique constraints that are supposedly standard for 2.28 are actually missing from the SL demo.

It’s to be noted that all “FOREIGN KEY” constraints are created during DHIS2 startup, if they don’t exist already. That’s the reason for all the duplicates - older versions (up to 2.22 or 2.23, I think) used intuitive names for the constraints like “fk_categories_categoryoptions_categoryid” listed above. After that the same constraints were inserted using autogenerated strings as names, like “fkf453b3bdd24076b3” - and it seems like these string names have been re-autogenerated again in later versions, causing the duplication.

“UNIQUE” constraints are, on the other hand, NOT created during startup (except for a few) if not existing - so here we are faced with the potentially more serious situation that many of them end up missing, potentially breaking functionality (example: yesterday it was impossible to create new users

in one of our instances because missing “UNIQUE” constraints had resulted in duplicated meta-data.

It is critical that somebody from the core team outlines the consequences of e.g. missing UNIQUE constraints, and in particular whether any ready-to-use tools are available for rectifying these data model inconsistencies.

Regards

Calle

···

Calle Hedberg

46D Alma Road, 7700 Rosebank, SOUTH AFRICA

Tel/fax (home): +27-21-685-6472

Cell: +27-82-853-5352

Iridium SatPhone: +8816-315-19119

Email: calle.hedberg@gmail.com

Skype: calle_hedberg