# Subsetting and foreign keys

## How subsetting uses foreign keys <a href="#subsetting-foreign-keys-about" id="subsetting-foreign-keys-about"></a>

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 [foreign-keys](../foreign-keys "mention").

## Adding virtual foreign keys from Subsetting view <a href="#subsetting-foreign-keys-adding" id="subsetting-foreign-keys-adding"></a>

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:

1. Display the table details panel for the table that contains the foreign key.
2. Click **Create Virtual Foreign Key**.
3. 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.
4. 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.
5. Click **Save**.

## Identifying and managing circular dependencies <a href="#circular-dependencies" id="circular-dependencies"></a>

### About circular dependencies <a href="#circular-dependencies-about" id="circular-dependencies-about"></a>

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 foreign key dependency&#x20;
between two tables](https://3378426797-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LSQCLFQ4bslJ-HYc8c3%2Fuploads%2FhvuEJgEWWkm59tFfkuVV%2FCircularFK_TwoTables.png?alt=media\&token=00f79e72-55df-4aae-a1c7-5a4eb3278a54)

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.

![Circular foreign key&#x20;
dependency&#x20;
within a table](https://3378426797-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LSQCLFQ4bslJ-HYc8c3%2Fuploads%2FXunEenD4sJag93osbwSy%2FCircularFK_OneTable.png?alt=media\&token=e831632e-5102-49a7-837d-70579372b92e)

### How Structural breaks a circular dependency <a href="#circular-dependencies-breaking" id="circular-dependencies-breaking"></a>

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 `NULL`able, 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 `NULL`able, then the circular dependency cannot be broken, and the subset generation fails.

### How to identify when Structural breaks a circular dependency <a href="#circular-dependencies-identify-breaks" id="circular-dependencies-identify-breaks"></a>

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.

<figure><img src="https://3378426797-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LSQCLFQ4bslJ-HYc8c3%2Fuploads%2FQxf3Cm77HO8WYND1MQg3%2FSubsettingGraphViewCycleBreakMarker.png?alt=media&#x26;token=1e64b0f0-e367-424c-9882-899916dbc231" alt=""><figcaption><p>Graph View table object with a<br>cycle break marker</p></figcaption></figure>

* The table details panel also indicates that there is a cycle break and lists the affected columns.

<figure><img src="https://3378426797-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-LSQCLFQ4bslJ-HYc8c3%2Fuploads%2FTJ4NEPDhQGtygRvIJG40%2FSubsettingTableDetailsCycleBreak.png?alt=media&#x26;token=994230bb-b219-46be-b21b-98441c216862" alt=""><figcaption><p>Cycle break information on a table details panel</p></figcaption></figure>
