Referential integrity means the relationships between tables stay consistent — every foreign key still points to a primary key that actually exists. Masking and subsetting break it when they change or drop key values table by table: a masked customer ID that comes out different in two tables no longer matches, and a subset that grabs orders without their customers leaves rows orphaned. Preserving it means applying the same transformation to every copy of a key and following relationships when you cut data, so the test database still behaves like production.
What referential integrity means in a test database
Referential integrity is the guarantee that the links between tables hold. A primary key uniquely identifies each row in a table — a customer_id in a customers table, one value per customer. A foreign key is a column in another table that references one of those primary keys — the customer_id on an orders row that records which customer placed the order. When a foreign key references one that isn't there — an order whose customer is gone — that row is an orphaned record, and the relationship it depended on is broken.
Test data depends on this because applications and test suites are written assuming the relationships hold. A join that expects every order to resolve to a customer, a cascade that walks from a customer down to their line items, a foreign key constraint the database enforces on insert — all of it assumes the graph is intact. When integrity breaks in a lower environment, the failure rarely looks like bad data. It surfaces as a query returning nothing, a constraint violation on load, or a feature that works in production and fails in staging — a bug hunt that ends at the data, not the code.
Consider a simple chain: a customer has orders, and each order has line items, each level referencing the one above it. Two steps in the end-to-end TDM workflow put that chain most at risk — masking, which changes values, and subsetting, which drops rows. Both sever those links when they treat each table in isolation.
Why masking breaks referential integrity
Masking breaks referential integrity when it transforms the same key inconsistently across the tables that share it. Masking replaces sensitive values with realistic substitutes, and keys are frequently sensitive — a customer_id derived from a national ID, an account number, an email used as a natural key. The problem appears when a key is masked independently in each table it lives in. If the customers table masks customer_id X into A, but the orders table masks that same X into B, the two copies no longer agree. Every order that used to point at that customer now points at a value that exists nowhere, and a join that was solid in production returns nothing in test.
The fix is consistent masking, also called deterministic masking: the same input value always maps to the same output value, everywhere it appears. Under deterministic masking, customer_id X becomes A in the customers table and A in the orders table and A in every other table that references it, because the transformation is a stable function of the input rather than a fresh random draw per table. The keys stay aligned, so the foreign keys still resolve after masking. It's the baseline requirement for masking a relational database without breaking its relationships, and the first capability to look for in any approach to data masking for lower environments.
Tonic Structural is built around this constraint. Structural applies consistent generators to primary and foreign key columns so a masked key comes out identical across every table that shares it, keeping the relationships intact through the transformation. That consistency is what separates masking built for test data management from a generic scrambler that transforms each column in isolation.
Why subsetting breaks referential integrity
Subsetting breaks referential integrity when it selects rows table by table without following the relationships between them. Subsetting extracts a smaller, workable slice of a large database — a few percent of production, enough to develop and test against without copying terabytes. The naive version grabs a fixed percentage of every table independently: five percent of customers, five percent of orders, five percent of line items. Those slices don't line up. Most of the sampled orders reference customers that weren't pulled into the five percent, and the line items reference orders that didn't make the cut. The result is a database full of orphaned records — technically smaller, functionally broken.
The correct method follows the foreign keys instead of ignoring them. You start from one or more anchor tables — the tables you're deliberately sampling from, such as a slice of customers — then traverse the foreign keys outward to pull in every row those anchors depend on: the orders belonging to each selected customer, the line items belonging to each order, and so on. Reference data is handled differently. A lookup table — a small, shared table of stable values like country codes, product categories, or status types that many rows reference — is copied in full rather than sampled, because most rows in the database depend on it and sampling it would orphan them.
| Technique | How it breaks referential integrity | How to preserve it |
|---|---|---|
| Masking | The same key is transformed differently in each table, so foreign keys no longer match their primary keys | Apply consistent (deterministic) transformations so a key maps to the same value everywhere it appears |
| Subsetting | Rows are sampled per table independently, leaving foreign keys pointing at rows that weren't included | Start from anchor tables, follow foreign keys to pull in every related row, and copy lookup tables in full |
| Undeclared relationships | Keys the schema never declared are ignored by the tool, so those links break silently | Define virtual foreign keys so the tool respects relationships the database doesn't enforce |
Relationship-aware subsetting scales to genuinely large systems. eBay scaled its multi-petabyte data ecosystem down to coherent, referentially intact subsets for development and automated testing — the payoff of following relationships rather than sampling blindly. The distinction holds across every approach to database subsetting: does the method respect the relationships, or treat each table as independent? For a deeper treatment of the technique, see data subsetting.
Foreign keys the schema doesn't declare
Both consistent masking and relationship-aware subsetting depend on one thing: the tool has to know what the relationships are. That knowledge usually comes from the foreign key constraints declared in the schema, which a good tool can traverse automatically. The trouble is that many real relationships are never declared. Teams disable foreign key constraints for write performance, enforce the relationships in application code instead, or span references across separate databases and services that no single schema describes. NoSQL and document stores often carry no formal foreign keys at all.
Undeclared relationships are dangerous precisely because they break silently. A tool that only reads declared constraints has no way to see an application-enforced link, so it transforms or drops those keys without applying the consistency the relationship needed — and nothing flags the omission until a downstream join fails. The fix is to define virtual foreign keys, sometimes called logical foreign keys: relationships you declare to the tool explicitly so it treats them exactly like schema-enforced ones, even though the database never enforced them. With the relationship made visible, consistent masking and relationship-aware subsetting both extend to cover it.
A related case is a circular dependency, where relationships form a loop — table A references table B, which references back to A, or a table references itself. The everyday version is a self-referencing table: an employees table with a manager_id that points at another row in the same employees table. Loops like these have to be handled deliberately during subsetting so traversal terminates instead of chasing the cycle forever. Tonic Structural documents how it handles both cases in its guidance on virtual foreign keys, including breaking circular dependencies so a subset stays finite and intact.
How Tonic Structural preserves referential integrity
Tonic Structural preserves referential integrity by applying the two disciplines above together — consistent transformation and relationship-aware traversal — as one coordinated process. On the masking side, Structural uses consistent generators on key columns, so when a primary key is transformed, every foreign key that references it is transformed the same way. A masked key comes out identical everywhere it appears, and the links survive the transformation rather than being broken by it.
On the subsetting side, Structural's subsetter starts from the anchor tables you choose and traverses foreign keys outward — both the keys declared in the schema and the virtual foreign keys you've defined — pulling in every related row a selected record depends on, and copying lookup tables in full so shared reference data is never partially present. Because the same consistency is maintained across tables and across linked databases, the relationships hold even when they span more than one source. Every one of these behaviors maps to the same goal: the smaller, safer database still behaves like production when an application runs against it.
The Tonic Advantage. Tonic Structural treats consistency and relationship traversal as a single job, not two settings you configure and hope agree. Consistent transformations keep a masked key identical across every table and linked database it appears in, while the subsetter follows declared and virtual foreign keys out from your anchor tables and copies lookup tables in full. The outcome for testing and QA is a test database that is smaller and safe to use, yet still passes the joins, cascades, and constraints your application depends on.
Masked production data vs. synthetic data for intact relationships
There are two routes to referentially intact test data, and they preserve integrity from opposite directions. The first is to transform production: mask and subset the data you already have, preserving the relationships that exist in the source. The second is to generate synthetic data whose referential integrity is built from the schema as the data is created, rather than inherited from a production copy. Neither is strictly better — the right one depends on whether you're starting from a production database or from a schema.
Transforming production, the domain of Tonic Structural, fits when you have representative production data and need a safe, realistic version of it that keeps its existing shape and relationships. Generating from a schema fits when there's no production data to start from, when the data is too sensitive to touch even in masked form, or when you need to scale record counts well beyond what production holds. Tonic Fabricate takes that generation route: it produces relationally intact synthetic data from scratch or by modeling an existing database, maintaining referential integrity across the tables, files, and APIs it generates. The two approaches are complementary — you can transform the production data you have with Structural and generate the cases it lacks with Fabricate — and choosing between synthetic and masked production data comes down to your starting point, not a verdict on either method.