When using the G-Migration+ feature, users can add customized referential integrity after importing the table schema. This is most commonly done when referential integrity does not exist between tables in the source database, and the user needs to perform data subsetting. 

It allows the user to manually add referential integrity for parent and child tables, which is applied during Data Subsetting. All other steps are the same when performing Data Subsetting. 

For example, a user wants to perform Data Subsetting for customer and account tables. However, the source database has no defined relationships for these two tables. The user can manually add customized referential integrity by performing the steps in this article. 

Note: Click here to learn more about Data Subsetting with G-Migration+.

In This Article

What is Referential Integrity? 

  • In databases, table relationships are defined by linking data in two or more tables. One table is the parent, and the other(s) are its children.

  • These relationships are defined using Primary and Foreign Keys within the database tables: 
    • Primary Key - A column within the Primary (Parent) table that is used to identify each table row (e.g., customer_id).

    • Foreign Key - A column in the Associated (Child) table that references the identifying value within the Parent table.

  • Referential Integrity refers to the accuracy and consistency of the defined relationships.

Example of Referential Integrity Between Tables

The "customer" columns within the account and address tables reference the primary key value for each customer record. This value is stored in the customer_id column within the customer table.

When Should Referential Integrity Be Added? 

  • Any time referential integrity does not exist between tables in the source database but needs to be present during Data Subsetting.

When Should Referential Integrity Not be Added? 

  • Any time the correct referential integrity has been defined in the source database for the imported table schema.

Does This Impact the Source Database? 

  • No, the added custom referential integrity only applies during subsetting and does not impact the source database. 

What Steps Must be Performed First?

  1. The user has set up a Project with the default Project Version.
  2. The user has created a file for each database (source and destination).
  3. The user has created an XTS file from their source database using the genrocket -xts command. 
  4. The user has accessed G-Migration+ for the appropriate Project and Project Version.
  5. The user has imported the table schema.

How to Add Custom Referential Integrity to Imported Table Schemas

  • Click Manage Referential Integrity in the G-Migration+ Dashboard to access the Manage Referential Integrity Dashboard.

  • Click the Add Referential Integrity button in Manage Referential Integrity Dashboard.

  • Select the Child Table in the source database (e.g., account).

    Note: The pagination buttons at the bottom of each form section can be used to view additional Tables/Columns. Only 10 items will be displayed at a time in each box.

  • Select the Child Column that will reference the column within the Parent Table (e.g., customer).

  • Select the Parent Table (e.g., customer).

  • Select the Parent Column (e.g., customer_id).

  • Click the Save button.

  • Click OK to close the notification message.

  • The child table name will be shown. The user can click on the child table name to see additional information.

    Note: Referential Integrity can be added to other child tables by following the same steps. 

How to Make Referential Integrity Changes for a Child Table

  • Click the Edit (Pencil) icon to modify Referential Integrity for a child table.

  • Click on the Child or Parent Table to view available Columns.

  • Make the needed changes and then click Update.

  • Click OK in the confirmation window.

How to Remove Referential Integrity from a Child Table

  • Click the Delete (Trash Can) icon to remove Referential Integrity from a child table.

  • Click Yes in the dialog window.