The ForeignKeys Sheet – Specifying which Foreign Keys to read from the Source Database

May 21, 2019
|By Thomas Pryce

Search Knowledge Base by Keyword

Created OnMay 21, 2019
byThomas Pryce
< Back

The ForeignKeys sheet displays the Foreign Keys that have been read from the source database. The ForeignKeys sheet information is populated by VIP using the GETMETADATA or GETKEYS action. This is run as part of a Basic Subset. The ForeignKeys sheet can then be edited.

The Foreign Keys determine which tables and rows are included in the Data Subset. The SUBSET. The BUILDMODEL Action automatically formulate rules for creating a Data Subset that will satisfy the relationships defined by Primary and Foreign keys. The SUBSET action will then “Crawl” up and down the tables collecting data until it produces a data set that satisfies all the hard and soft relationships.

The Foreign Key constraints will not themselves be included in the Data Subset by the core actions involved in running a Subset (GETMETADATA, PREPENV, BUILDMODEL, and SUBSET). To include the Foreign Keys specified in this sheet, you must perform the Post-Subset action of ADDFKEYS. This should be performed after the SUBSET Action and ADDPKEYS or the composite ADDKEYS Action. ADDFKEYS should be performed after the SUBSET Action and ADDPKEYS Action. Foreign Keys can also be validated post-Subset to check that they are all unique.

ForeignKeys can be toggled on and off from the ForeignKeys sheet, specifying whether Subset Rules will be built to satisfy a given Foreign Key. Each Row of the ForeignKeys sheet represents a different Foreign Key that has been read from the source database. The Columns are:

  1. Active: Setting Active to “Yes” or “No” includes or excludes a relationship.
  2. FK_Schema: The source Foreign Key schema.
  3. FK_Table: The Foreign Key table name.
  4. FK_Columns: The Column Names. For composite keys the column names are delimited by a semi-colon.
  5. PK_Schema: The source Primary Key schema
  6. PK_Table: Primary Key table name
  7. PK_Columns: The Primary Key Column Names. For composite keys the column names are delimited by a semi-colon.
  8. FK_Name: The Foreign Key name.

An example ForeignKeys sheet appears as follows:

Data Subsetting