Subsetting and foreign keys
How subsetting uses foreign keys
The subsetting process uses foreign keys to navigate the relationships in your data. It uses these relationships to identify the data to include in the subset. Without foreign keys, Tonic Structural does not know how to navigate the relationships in your data. Properly configured foreign keys allow Structural to select the necessary rows from other tables, which ensures referential integrity.
Foreign keys are often set up directly within the source database. You can also set up virtual foreign keys within Structural. For example, a foreign key relationship might be missing, or your database might not use foreign keys. If your database uses polymorphic keys, then you must use the foreign key upload to add those keys manually.
For information about Foreign Keys view, including how to create and upload virtual foreign keys, go to Viewing and adding foreign keys.
Adding virtual foreign keys from Subsetting view
You can also add virtual foreign keys from Subsetting view.
For example, on Graph View, you might notice that a relationship between tables is missing. You can immediately add a virtual foreign key to establish that relationship.
To create a virtual foreign key from Subsetting view:
Display the table details panel for the table that contains the foreign key.
Click Create Virtual Foreign Key.
Under Foreign Key from this Table, select the column in the current table that contains the foreign key. To find the foreign key column, begin to type the column name.
Under Primary Key in Another Table, select the column that contains the primary key. To find the primary key column, begin to type the column name or the name of the table.
Click Save.
Identifying and managing circular dependencies
About circular dependencies
Foreign key relationships can sometimes have circular dependencies, also referred to as cyclical dependencies.
In the simplest case, a circular dependency occurs when two tables each contain a foreign key that references the other table. In the following example, the Employees table contains a department_id foreign key column that references the Departments table, and the Departments table contains a manager_id foreign key column that references the Employees table.
Circular dependencies can also come from a much longer chain of references, where you follow references through several tables before returning to the original table.
Circular dependencies can also occur when a table references itself. In the following example, the Employees table contains a manager_id foreign key column that contains an employee ID value from the id column.
How Structural breaks a circular dependency
During subsetting, if the circular dependency isn't broken, then there is an endless loop of going back and forth between the tables that reference each other.
To break a circular dependency, Structural identifies a foreign key column that is NULLable, and sets its values to NULL. When the process reaches a NULL value, it stops looking for additional related records. Structural applies the minimum number of NULL values that are needed to break the circular dependencies.
If none of the foreign key values are NULLable, then the circular dependency cannot be broken, and the subset generation fails.
How to identify when Structural breaks a circular dependency
Tonic can detect circular dependencies before you run subsetting.
When a table contains foreign keys that are part of a circular dependency that Structural breaks:
On Graph View, a Cycle Break marker is added to the table object. The marker includes the name of the foreign key column.
The table details panel also indicates that there is a cycle break and lists the affected columns.
Last updated
