Struggling with DB Table FK associations

[Guillaume]

Hi,

I have a data model created from a DB reverse engineering which is being updated.
We have a custom tool that extracts the FK connectors details for a 3rd party inhouse tool.

On most FK associations, the connector name matches the middle label i.e. the PK/FK mapping.
As users update the DB model e.g. by changing PK definition or creating FK from new tables, we noticed that some FK associations have an empty name.
Note: users open or create the FK association, and change the PK/FK mapping definition via the FK constraint popup which updates the FK constraint /operation on the table.

Querying the t_connector table, the Name is indeed null for a number of FK associations, whilst the mapping value is available in the top_mid_label field.
I ran a quick test creating 2 tables from scratch with a PK + FK. The mapping information e.g. id = tbl1id is visible on the diagram. Looking at t_connector table, both Name and Top_Mid_Label are empty so I don't understand where the mapping is stored.
t_connector StyleEx has the FK definition (FKINFO=SRC=...;DST=...), SourceRole has the FK constraint/op name, and TargetRole has the PK constraint name.

Looking further into the EA db, I also noticed the FK stereotype is not always the same in t_xref table
Description value from the reverse FK association: "@STEREO;Name=FK;GUID={6027B827-011A-4e54-88E8-C8FB520A6C0B};@ENDSTEREO;";
Description value from the manually created FK association: "@STEREO;Name=FK;FQName=EAUML::FK;@ENDSTEREO;";

If I change the manually created FK association to @STEREO;Name=FK;GUID={6027B827-011A-4e54-88E8-C8FB520A6C0B};@ENDSTEREO;" and update the association, then the name is properly updated in the DB.

I looked up t_stereotypes table; the built-in FK stereotype doesn't match this GUID and I noticed the GUID is different between EA projects.

Update: I'm trying to find a solution with a script to fix the associations matching the manual update in EA for all FK connectors with an empty name.

[Paolo F Cantoni]

Hi Guillaume,
The t_xref.description with the GUID is caused by local stereotypes "getting in the way" of the MDG-based stereotypes.  Because they are local to each repository, they can vary over time and with movement between repositories.  We "jam" such t_xref entries with the correct MDG-based value to ensure Consistency, Consistency, Consistency! ^TM!


This may help to resolve some of the ongoing issues.  If the null-valued properties are related to GUID-based relationships, you may be able to synthesise the required values from the available information SRC, DST etc.  That way, you bring everything "back into line".


HTH,
Paolo

[Guillaume]

Hi Paolo

Thank you for your reply.
Am I right to understand that the GUID in t_xref.Description should match the FK GUID from t_stereotypes table in the active EA project, considering that I have no MDG and the FK stereotype I want to use is the default built-in one ?
I tried to find the GUID from an installed MDG, but I can't find where Model MDG are stored in EA db (I thought it was in t_document)

[Paolo F Cantoni]

Hi Paolo

Thank you for your reply.
Am I right to understand that the GUID in t_xref.Description should match the FK GUID from t_stereotypes table in the active EA project, considering that I have no MDG and the FK stereotype I want to use is the default built-in one.
I tried to find the GUID from an installed MDG, but I can't find where Model MDG are stored in EA db (I thought it was in t_document)

Perhaps, Guillaume, but that's not relevant in my view.
Once you're creating REAL FK constraints, you should use EAUML::FK as the t_xref.Description field  e.g
@STEREO;Name=FK;FQName=EAUML::FK;@ENDSTEREO;
We actively remove any t_stereotypes entries that conflict with any MDG-based stereotypes.  This removes ALL sorts of problems.

Does that clarify?
Paolo

[Guillaume]

Hi Paolo,

Thank you for your feedback.
I managed to find a solution with a script that fixes most FK associations by using the association role names to find the matching PK/FK constraints on the respective tables, identify the PK/FK mapping, and update the connector name + labels.

Note that this situation is due to a custom tool via the API that updates in bulk tables primary and foreign keys. As a result some of EA behaviour is missed out and not updated correctly, leading to some inconsistencies. It would work if everything was done in EA.

[Guillaume]

A strange behaviour I noticed even without any customization is to do with the FK association name
- Create 2 tables e.g. MySQL with a couple of attributes, and composite primary keys e.g. Tbl1.ID1, ID2.
- Create an association from Tbl2 to Tbl1 to create the FK, map the FK and PK columns

If I query EA DB, the connector name is empty, even though the mapping is properly rendered on the diagram, or on the table element properties view.
It's only if I try to change the connector name via the properties view, or update the cardinality that the value is copied and updated in the connector's name.

We use FK association names to extract mapping information.
Is there a workaround to force a refresh via the API ?

Update: I created a blank EA project with the 2 tables + FK, and looked at the EA DB content: the mapping information visible on the FK association e.g. tbl1ID1 = id1 tbl1ID2 = id2 is not stored in the db. So my understanding is that this information is dynamically rendered on the diagram based on the FK association definition. I don't understand why it is not populated right from the start, and only after a specific event related with an update on the connector e.g. cardinality update. Some users identified it as a bug. Could it be fixed ?