Hello Bryan,
I think you may already have gotten an answer over on ticket, but let me put back the elements here if it can interest the rest of the community.
The bottom line is that the foreign key value should be the primary key of the parent entity. There are many ways to set a primary key, so it can be up to you to determine the best attribute to use. For information on how to do this, please refer to the documentation here: https://www.semarchy.com/doc/semarchy-xdm/xdm/latest/Design/logical-model/create-entities.html#_create_a_new_entity
If your need is that you have multiple tables, and each table wants to join on a particular column, i.e Sales table joins on CountryID, Customer table joins on CountryName, Product table joins on CountryAlpha2Code.
This is not achievable with the current design of the solution.
As only 1 value is stored in a foreign key: the primary key of the parent entity.
So, this needs to be addressed directly in the integration process.
If 1 system is sending the CountryName then they need to do a lookup on the Country entity to bring back its ID, and so on and so forth.
Bryan LEE
I am enquiring if Semarchy XDM has a way to choose the attribute to be used as a Foreign Key for the Reference Relationship.
An illustration:
I have a Country table, with the following attributes
CountryID (ID, auto-increment start from 1)
CountryName
CountryAlpha2Code (unique codes, e.g., US, UK, SG).
I want a reference relationship with a Business Unit table. The Business Unit table has an entity column where there are CountryAlpha2Codes like US,UK,SG.
How do I create a Reference Relationship between these two tables? Alternatively, can a Reference Relationship in xDM be configured to use CountryAlpha2Code (non-ID) as the foreign key, or must it always store the Country entity’s ID attribute?
If so, how do i proceed to map/import CountryAlpha2Code in BU so that the BU → Country reference is set?