How to Apply Naming Standards to FK Constraints in erwin Data Modeler
One area where many erwin Data Modeler users struggle with naming standards is that of foreign key constraints.
Looking at the relationship editor in the logical model, the first thing we see at the top of the dialog is a label made up of the parent entity name, the parent-to-child verb phrase and the child entity name. This is just what most people would like to see used as the basis for a physical constraint name.
Unfortunately, what erwin actually uses is the relationship name you see directly beneath it. This property is not shown on the logical model diagram and tends to be left to default, namely R/n, where n is a sequential number within the model.
Even worse, this is one of the few properties that is both logical and physical. We can see the implications for this if we look at the Name Mapping tab of the Model Naming Options dialog.
What we see here is that, unlike most of the other mapped properties, there is no translation macro from the logical to the physical. It is the same property. That is, the relationship name is the fk constraint name. This means we cannot reference this property in any macro used to derive a new name.
What we can do is use the parent and child table names. We can also use verb phrases, but, these being optional, we cannot rely on them as a differentiator between multiple relationships involving the same pair of tables.
So, the solution we normally suggest is to use the relationship id (%relid) or an abbreviated form of it (it is a GUID consisting of 4 groups of 4 hex pairs!) in combination with the parent and child table names, again appropriately abbreviated.
What we end up with is a macro such as %parent_%child_%substr(%relid,1,4) to insert into the macro column of the Name Mapping tab of the Model Naming Options dialog.
Note that, because %parent and %child relate to table names, any abbreviation or other change applied in the mapping of entity to table names has already been done. So, a relationship CUSTOMER places ORDER can result in a constraint named CUST_ORD_7a5a.
If you found this article helpful, why not take a look at our training or consultancy offerings?
erwin Data Modeler
erwin Data Modeler is the industry-leading data modelling solution that enables organisations to discover, design, visualise, standardise and deploy enterprise data through an intuitive, graphical facility built on industry standards and best practices.
