First - if the
Patient table has an internal unique ID (often, but not always, an integer value), and a distinct real-world ID, then there are a few rules that should normally be followed:
- The internal ID is normally going to be the primary key; however, the "real_world" ID should ideally be constraint to be unique.
- The application should allow the user to enter the real-world ID in order to fin the patient in the system.
- Ideally, the internal ID should not be displayed to the user - because they don't have any real need for it.
Also, of course, if the patient is not in the system at all, the application should allow a new patient to be added, and (if necessary) be able to create or retrieve a new valid
So, applications should be designed so that the correct foreign record can be located by the values in said record that are meaningful outside the system, and then (based on the record selected by the user) should get the internal ID of the record, and store it as the desired foreign key.
Side note: For me, the best reason to have an ID value that is not meaningful to users is that anything that's meaningful to users can change.
As an example: in a system I used to work with, the unique ID for an employee was used as their username in the system. This value was usually formed by combining the employee's location ID with their initials.
What this lead to was a desire to change this ID when a user changed their name (got married or divorced, for instance), or changed their location. The process of changing an employee's ID touched over a dozen tables, and potentially tens of thousands of rows; so, updating the employee's ID could take 10-15 minutes, and would frequently cause blocking of many other users. On average, there was a change that actually impacted the users at least once a month.
If the vendor had one field for the employee's userid, and a separate field (that no user ever needed to see) for the employee's ID, then these delays wouldn't have happened.