Question About Lack of Database-Level Foreign Key Constraints in OpenEMR

I’m new to OpenEMR and have noticed something intriguing about the database structure that I’m hoping to understand better. It appears that there are no database-level constraints enforced between foreign keys, and I’ve also observed inconsistencies in column data types across related tables.

Can anyone explain why the decision was made not to enforce these foreign key relationships at the database level in OpenEMR? From my understanding, implementing such constraints could potentially enhance data integrity and reduce errors.

Additionally, if there are specific reasons or benefits behind allowing different data types for columns that are supposed to be related, I would appreciate insights on this as well.

Thank you for your help!

If you search foreign keys on the forum you’ll see a couple of posts about this topic. This is a good discussion I did back in 2019 about it.

I started down that path but stopped as there were more pressing projects that needed to be implemented.

1 Like

Another good reference point is a project I did recently that at least provide an updated ERD on the relationships of the DB for everything patient related.

Explanation of the EHI export documentation is here.

1 Like

From DBA perspective OpenEMR database structure is stuck somewhere between “if ain’t broke” and “what about”. As a result many checks that can be done globally and consistently, are currently done in code (maybe). That implies adding a constraint at this stage would also need a robust mechanism to let practices fix some potentially problematic historical data and follow it up with review of the code. Not a trivial task!