How to Apply Naming Standards to FK Constraints in CA ERwin Data Modeler

One area where many people struggle with ERwin 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.

 

One Response to How to Apply Naming Standards to FK Constraints in CA ERwin Data Modeler

Leave a Reply

Your email address will not be published. Required fields are marked *