Criteria used by erwin to infer PKs, FKs, Relationships during RE...

Description:

During Reverse Engineering, when using Infer, we get mixed results.  What is the criteria used by erwin in determining the Primary Keys (PKs), Foreign Keys (FKs) and Relationships?  

Solution:

This is also detailed in Help, but here is the criteria used by erwin in determining how to mark a column as a PK, add a Relationship and migrate that to a Child Table as a FK. 

Primary Key:

Note that the primary key is inferred when the table is imported and created.  If a table includes one index that is defined as unique or primary for that table, the PK is determined based on the member of that index.

If multiple tables have the same primary key, the application searches for other criteria such as the number of primary keys each table has. Suppose that one table has just one primary key, the other one has multiple primary keys, and a primary key is common for the tables. In this case, the table with one primary key is created as the parent table. In general, if there are multiple tables with the same primary key and the application cannot determine the relationship with certainty, then the application does not create the relationship.

Relationships:

Infer from Indexes:

If a table includes an index that includes the same index members in the same order as an index identified as a primary key in a different table, and also includes additional columns not found in the primary key, it adds a Relationship making the matching column a FK.  In fact, the FK column is migrated from the Parent table, overriding the existing column. 

Infer from Names:

If a table includes an index that has the same name as an index identified as a primary key in a different table, it adds the Relationship. 

Parent/Child

After inferring a relationship, it is determined that the larger index (more columns) belongs to the child table and the smaller index (fewer columns) belongs to the parent.

Foreign Key:

After inferring a relationship, it is determined that the index members in the child table that match those in the primary key of the parent table comprise the foreign key.  See the information above under Relationships about the migration. 

Identifying or Non-Identifying:

After inferring a relationship, if the index columns do not appear in the primary key in the child table, it is determined that a non-identifying relationship exists.  Similarly, an identifying relationship exists if the index columns do appear in the primary key in the child table.

If you infer primary keys or relationships for a database and they are defined in the database, the application attempts to infer more information. For example, when a schema contains information about a primary key and it is not explicitly defined for a table, then the primary key is inferred automatically.

Note: In general, the criteria that are used for inferring primary key and relationship information leads to correct inferences. However, there is no guarantee that these inferences are always correct.