applies to: Indyco Builder 1.47+


During the process of data warehouse reverse engineering, Indyco by default reads the tables schema including the primary key and foreign key physical constraints.





With the option 'Enable integrity constraint inference', Indyco can infer primary key and foreign key constraints.



Primary key inference 


Primary key inference need to define a primary key pattern. 


For example:


Primary key pattern: {table}_ids


{table} is a key word of Indyco which represents the table's name.


This primary key pattern assumes that for each table named '{table}'  there is a primary key column named '{table}_ids'.

Ex. for a table named 'Order', exists a primary key column named 'Order_ids'.


Indyco displays the primary key constraints with the following icons:


Primary key defined on physical layer

 Primary key inferred by Indyco


 



Foreign key inference


Foreign key inference is based on column names and primary keys. 


The reverse engineering process explores the physical model from the fact tables and infers a relation between two tables with the following rules:


  • The tables does not already have a relation between them;
  • The tables have one or more columns with the same name and at least one of these tables have those columns such as primary key;


Example:

  • There are two tables 'A' and 'B';
  • Table 'A' contains a column 'B_ids' and the table 'B' contains a column named 'B_ids';
  • Column 'B_ids' is primary key in table 'B'
  •  if a foreign key does not exist betweet 'A' and 'B' a new foreign key will be created.


Indyco displays the foreign key constraints with the following icons:


Foreign key defined on physical layer

Foreign key inferred by Indyco