Synthetic Data Foreign-Key Failures: Causes, Detection, and Safe Fixes

iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more

A synthetic child row has a foreign-key failure when its non-NULL key does not match a candidate or primary key in the referenced parent table. The reliable fix is not to replace the value with any key that happens to exist. Generate parent rows first, pass their actual generated keys to the child generator, validate every relationship after loading, and repair only with keys and nullability rules that preserve the intended test scenario.

This guide explains why generated data produces orphaned keys, how to detect them before release, and how to handle empty parents, composite keys, type mismatches, cycles, and bulk loads that temporarily disable enforcement.

#	Preview	Product	Price
1		Grokking Relational Database Design	$37.66	Buy on Amazon
2		Learning SQL: Generate, Manipulate, and Retrieve Data	$36.49	Buy on Amazon
3		Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data	$19.99	Buy on Amazon
4		SQL Database Query Programmer T-Shirt	$19.99	Buy on Amazon

What a foreign-key failure means

A foreign key (FK) is valid only when its value is NULL where NULL is allowed, or when it equals a candidate key—normally the primary key—in the referenced parent table. A child row with a non-NULL value that has no such match is an orphan. The database is protecting referential integrity: every relationship represented by a child value must point to an existing parent row.

PostgreSQL’s tutorial shows the characteristic failure: ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey", followed by DETAIL: Key (city)=(Berkeley) is not present in table "cities". The constraint name identifies the relationship; the detail identifies the child key that could not be found.

#1 Best Overall

Sale

Grokking Relational Database Design

$37.66

Buy on Amazon

In a synthetic-data pipeline, the same condition can arise even when the schema is correct. Generators may create tables independently, use a key list from a different environment, remap parent identifiers without remapping children, or load child rows before their parents. The resulting data may look realistic while still being structurally impossible to insert.

Why synthetic generators create orphaned keys

Tables are generated independently

Independent table generation is the most common design error. If a child generator invents customer IDs, while the parent generator independently invents customer IDs, the two sets will almost never be identical. Sampling from a production extract or a previous test run creates the same problem when the parent set has changed.

A dependency-aware generator instead analyzes the schema graph. It generates a parent table, records the keys actually produced, and gives that key pool to the child generator. Synthesized documentation describes this model as generating parents before children and having child foreign-key generators refer to the parent key. The principle applies whether keys are integers, UUIDs, strings, or multi-column tuples.

Generation or load order violates the dependency graph

For a simple relationship, the safe order is parent, then child. A loader that inserts child rows first receives an immediate constraint error, even if the parent rows are present in the same batch but have not yet been committed. A pipeline that creates files in arbitrary alphabetical order can therefore fail despite containing all required rows.

Order must follow the directed graph of foreign keys. If orders.customer_id references customers.id, customers must be materialized before orders. The same rule applies recursively to grandparents and lookup tables.

Parent IDs are remapped without updating children

Data masking, sequence reseeding, UUID conversion, and environment-specific key mapping can change parent identifiers. If only the parent column is transformed, existing child values become stale. A mapping created during transformation must be applied to every referencing column, not just to the parent table’s primary key.

The parent table is empty

An empty parent creates a deliberate policy decision. A nullable child foreign key can be emitted as NULL when no parent exists; that represents an absent relationship, not a successful reference. A non-nullable child foreign key has no legal value if the parent set is empty, so a dependency-aware generator must fail the transformation or create the required parent fixture. Treating an empty key pool as a signal to invent an arbitrary value only moves the failure to load time.

Composite keys are only valid as complete tuples

For a composite foreign key, every participating column must match the corresponding column of one parent key tuple. Matching each column against a separate parent row is not sufficient. For example, (tenant_id, user_id) must match one existing pair; a tenant from one row combined with a user ID from another is an orphan even when both individual values appear in the parent table.

Type, collation, whitespace, and signedness differ

Values can appear identical while comparing differently. Common causes include an integer child column mapped to text, signed and unsigned numeric differences, UUIDs serialized with different formats, trailing spaces in fixed-width character fields, and case or collation differences. Normalize representations before diagnosing the generator. A key copied as 00123 is not automatically equivalent to numeric 123, and a case-sensitive comparison can distinguish ABC from abc.

Cycles make ordinary ordering impossible

A cycle exists when tables depend on one another, directly or through a chain—for example, an employee row references a department manager while the department references an employee. There is no order that inserts every row with checks enabled unless one side is initially NULL, a constraint is deferred, or the fixture is redesigned. SAP guidance describes ordering tables where possible and deferring checks until commit for cyclic relationships.

Bulk loading bypasses enforcement

Some loaders disable checks to permit out-of-order reloads. MySQL’s foreign_key_checks setting, for example, allows operations to proceed without foreign-key enforcement while it is disabled. This does not repair invalid data; it merely suppresses the signal. A dataset loaded this way must undergo explicit orphan validation after checks are restored.

Rank #2

Sale

Learning SQL: Generate, Manipulate, and Retrieve Data

$36.49

Buy on Amazon

Detect failures before releasing generated data

Use constraints as an early alarm, then run explicit data-quality queries. A robust workflow is:

1. Validate in an enforcing environment. Keep foreign-key constraints enabled whenever possible. Capture the database error, constraint name, child table, parent table, and offending value. The constraint name often reveals which generator mapping is wrong.
2. Run an anti-join for every relationship. The following query returns non-NULL child keys with no matching parent key:

SELECT c.fk
FROM child AS c
LEFT JOIN parent AS p ON p.pk = c.fk
WHERE c.fk IS NOT NULL
  AND p.pk IS NULL;

An empty result is the expected result. Run the query against the exact schemas and columns used by the load, including any tenant or partition predicate.

Check composite foreign keys as tuples

Compare all columns in one join, and report the complete offending tuple:

SELECT c.tenant_id, c.user_id
FROM child AS c
LEFT JOIN parent AS p
  ON p.tenant_id = c.tenant_id
 AND p.user_id = c.user_id
WHERE c.tenant_id IS NOT NULL
  AND c.user_id IS NOT NULL
  AND p.tenant_id IS NULL;

Your null predicate should follow the schema. If one component of a composite key may be NULL, verify the database’s constraint semantics and the fixture policy rather than assuming that a partially NULL tuple is a valid relationship.

Check types and canonical forms

• Compare data types, length, precision, signedness, and UUID representation on both sides.
• Check collation and case sensitivity for character keys.
• Trim or otherwise normalize whitespace only if that matches the production schema’s semantics.
• Inspect implicit casts: a query that succeeds because of a cast can hide a load that will fail when the target column is stricter.

Compare key domains and row counts

Count parent rows, child rows, distinct child keys, and distinct parent keys. Compare minimum and maximum values only as a diagnostic; range overlap does not prove referential validity. A child key domain larger than the parent domain, or a sudden drop in parent cardinality, often identifies a truncated fixture or an incorrect filter.

Inspect the dependency graph and generator configuration

Confirm that every child generator receives the key pool produced by the same run, not a cached list from another seed or environment. Record the generator version, seed, table order, filters, and key-mapping configuration. Reproducibility matters: a failure that disappears when the seed changes is still a defect if the pipeline cannot guarantee valid output for all supported seeds.

Validate after a bulk load

If checks were disabled, re-enable them before publishing the dataset and run every anti-join. Some systems reject or roll back operations that violate integrity when checks are enforced. Do not infer validity from a successful bulk-load exit code.

Design a dependency-aware generation pipeline

1. Extract the schema graph

Read primary keys, candidate keys, foreign keys, nullability, and referential actions from the target schema. Represent each edge as child columns pointing to one parent key. Include cross-schema and cross-database relationships when the load spans more than one target.

2. Establish a deterministic parent policy

Decide how many parent rows each fixture requires, which keys are generated, and whether key values must be stable across runs. A deterministic seed is useful for reproducing failures, but stability must not come from hard-coded child values that bypass the current parent output.

3. Generate roots and expose actual keys

Generate tables with no incoming dependencies first. Store the keys that were actually written, including composite tuples. Do not reconstruct a key pool from an intended range if the generator can skip values, apply filters, or use database-generated identities.

4. Generate children by sampling the current key pool

For each child row, choose a key from the corresponding parent pool. Preserve the intended distribution—such as many orders per customer—without introducing values outside the pool. If the child relationship is optional, deliberately choose NULL according to the test scenario’s proportion; do not use NULL merely because parent generation failed.

Rank #3

Sale

Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data

$19.99

Buy on Amazon

5. Propagate mappings during transformation

When a parent key changes, maintain an old-to-new mapping and apply it to every child column that references it. For composite keys, map and replace the complete tuple atomically. Reject ambiguous mappings rather than selecting one parent arbitrarily.

6. Load in topological order

Insert parent fixtures before dependent fixtures, commit boundaries included. For large loads, batch within that order and make retries idempotent so a partial failure cannot leave an untracked mixture of old and new key pools.

7. Validate inside a transaction

Run anti-joins and constraint validation before committing a release dataset. If validation fails, roll back or quarantine the batch, retain the offending key and generator seed, and fix the source mapping rather than editing rows manually without an audit trail.

Safe ways to repair invalid generated data

Choose a repair based on the scenario the fixture is meant to represent. The objective is to restore a truthful relationship, not merely to make an insert statement succeed.

Rebuild child keys from the parent pool

For an accidental orphan, replace the child key by sampling from the matching parent-key set generated for that run. Preserve the intended distribution where possible—for example, do not collapse thousands of orders onto one customer unless that concentration is part of the test. Record the replacement mapping and rerun validation.

Restore a missing parent fixture

If the child row represents a real business entity that should exist, regenerate or restore its parent row, then any ancestors required by that parent. This is preferable to changing the child when the scenario specifically tests a known customer, account, or project.

Use NULL only for an optional relationship

Set an orphaned child value to NULL only when the column is nullable and the test case is intended to model “no parent.” A NULL bypasses the equality match; it does not prove that the relationship is valid. Never use it to satisfy a non-nullable foreign key or to conceal a required fixture.

Remove dependent rows for an intentional deletion scenario

If the fixture models deletion, remove or transform dependent children according to the schema’s documented policy. Deleting a parent while retaining children is valid only when the relationship’s action and business semantics explicitly allow it.

Normalize types and mappings

Correct the generator or transformation layer so both sides use the target schema’s type, collation, encoding, and canonical format. Then regenerate affected rows. A one-off cast or whitespace trim in a validation query can hide a persistent production failure.

Regenerate complete composite tuples

Do not repair one component independently. Select an existing parent tuple and replace every child component together. Validate the tuple with a join on all columns.

Handle cycles deliberately

First, redesign the fixture graph if the cycle is not needed. You may split creation into phases, insert one nullable edge later, or use a stable root row. If the cycle is intrinsic, use a database-supported deferred or commit-time check, load rows in a documented sequence, and validate before commit. Deferral is a controlled transaction strategy, not permission to release unchecked data.

Rank #4

SQL Database Query Programmer T-Shirt

• Database Programming design. Funny database SQL joke that makes a great gift for database administrators, programmers or computer scientists. Fun gift for database administrators, programmers and hackers who like to wear funny nerd clothes.
• Funny gift for men and women who love SQL. The perfect SQL Query top for programmers, hackers and SQL database fans who love relational databases.
• Lightweight, Classic fit, Double-needle sleeve and bottom hem

$19.99

Buy on Amazon

Referential actions are part of the fixture contract

Microsoft documents four common actions: NO ACTION, CASCADE, SET NULL, and SET DEFAULT. The correct action depends on whether the synthetic scenario models retention, deletion, or reassignment.

Do not choose an action simply because it makes a generated load pass. An action that silently deletes children or points them to a default parent can invalidate the behavior your test is supposed to measure.

Bulk loads, deferred checks, and recovery

Disabling checks can be useful for a controlled reload when the database supports it, but it changes the safety guarantees of the operation. Isolate that phase, document the setting, and make post-load anti-joins mandatory. Re-enable enforcement before downstream consumers receive the data.

For cyclic schemas, a deferred constraint checks at transaction commit instead of at each statement, where supported. This permits a valid final state to be assembled in multiple steps. It does not allow a transaction to commit with an orphan. If the database does not support deferral, use a fixture redesign, staged updates, or a temporary nullable edge with an explicit final validation.

When a load fails, keep the transaction or batch that produced the error identifiable. Capture the constraint name and offending tuple, then inspect whether the parent row was absent, filtered out, transformed, or merely uncommitted. Avoid repeatedly rerunning a non-idempotent loader; duplicate parents or children can obscure the original defect.

How to evaluate a synthetic-data tool or pipeline

Feature checklists are less useful than asking how a tool behaves under the failure modes above. Evaluate these capabilities with a representative schema, including an empty parent, a composite key, and a cycle:

Synthesized positions its TDK for very large test-data databases with complicated primary–foreign-key relationships. That positioning is relevant only if the implementation meets the validation and recovery requirements above; dependency awareness should be demonstrated on your schema rather than assumed from a product description.

Cloud databases enforce the same principle. Google Cloud Spanner documents foreign keys that reject updates breaking referential integrity. Moving generation or loading to a different database does not remove the need for parent-first generation and orphan checks.

Worked diagnosis: from error to repair

Scenario

A generated orders file contains customer_id = 8421. The target customers table contains 10,000 rows, but no row with ID 8421. The load reports a foreign-key violation.

Diagnosis

1. Use the constraint name to confirm that orders.customer_id references customers.id.
2. Run the anti-join and confirm that 8421 is an orphan rather than a type or collation mismatch.
3. Compare the generator logs. If the child file sampled a cached key list while the parent file was regenerated, the dependency boundary is the defect.
4. Check whether 8421 was supposed to represent a known customer or was just a random value.

Repair

If it was random test data, regenerate the child value from the current customer-key pool and preserve the desired order distribution. If it represented a required customer scenario, restore customer 8421 and any required ancestors. If the order is intentionally unassigned and the column is nullable, set the value to NULL and mark that case in the fixture definition. In all three cases, rerun the anti-join and a full constraint validation before release.

Release checklist

• Parent tables are generated and loaded before dependent tables.
• Every child key comes from the current run’s parent-key pool or is an explicitly allowed NULL.
• Composite foreign keys are validated as complete tuples.
• Types, collations, whitespace, encoding, and signedness match the target schema.
• Empty-parent behavior is defined separately for nullable and non-nullable relationships.
• Cycles use a documented redesign or supported deferred-check strategy.
• Any temporary check disablement is recorded, reversed, and followed by anti-joins.
• Referential actions match the business scenario instead of merely suppressing errors.
• Generator seed, schema version, mappings, and offending keys are captured for replay.
• Validation runs inside a transaction or release gate, and failed batches cannot reach consumers.

The practical rule is simple: foreign-key errors are signals that the synthetic data’s relationship graph and its rows disagree. Preserve the graph, generate from real parent keys, and make post-load validation non-optional.

Quick Recap

SaleBestseller No. 1

Grokking Relational Database Design

$37.66

SaleBestseller No. 2

Learning SQL: Generate, Manipulate, and Retrieve Data

$36.49

SaleBestseller No. 3

Practical SQL, 2nd Edition: A Beginner's Guide to Storytelling with Data

$19.99

Bestseller No. 4

SQL Database Query Programmer T-Shirt

Lightweight, Classic fit, Double-needle sleeve and bottom hem

$19.99

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.