Testing referential integrity (2026)

TL;DR

Testing referential integrity means proving every child relationship points to an allowed parent throughout inserts, updates, deletes, concurrent transactions, migrations, and recovery. Validate database constraints and application behavior, then use independent orphan and cardinality queries as postcondition checks.

Key Takeaways

• Derive relationship tests from the real schema and business lifecycle, not foreign key names alone.
• Verify valid and invalid inserts, parent-key updates, every delete rule, nullability, and composite-key behavior.
• Use independent anti-join queries to prove that no accessible orphan records exist after each risky operation.
• Exercise concurrency and transaction boundaries because application prechecks cannot replace atomic database enforcement.
• Test soft deletes, polymorphic links, external references, and historical records even when no physical foreign key exists.
• Run integrity gates before and after migrations, imports, restores, retries, and rollback procedures.

Testing referential integrity means proving that related data stays valid through every operation that can create, change, hide, or remove a relationship. The core checks cover valid and invalid foreign keys, update and delete actions, nullability, composite keys, concurrent transactions, migrations, imports, soft deletes, APIs, and recovery.

A schema can reject a simple orphan and still fail the business. A cascade may remove audit history, a soft-deleted parent may remain selectable, an import may disable constraints, or two concurrent requests may defeat an application-level precheck. This guide builds a layered QA strategy with executable PostgreSQL examples and interview-ready reasoning.

TL;DR

Use database constraints as the primary atomic safety net where the data model permits them. Add service-level validation for usable errors and lifecycle rules, but verify the database independently after risky workflows.

1. Testing Referential Integrity Starts With Relationship Semantics

Referential integrity is the rule that a reference identifies an allowed parent row or follows a defined nullable or historical exception. A physical foreign key is the database mechanism most teams recognize, but the business relationship is broader. Tenant boundaries, soft-delete state, effective dates, ownership, polymorphic targets, and references to external systems may not fit a simple key constraint.

Begin with a relationship inventory. For every child reference, record parent and child tables, column pairs, data types, nullability, uniqueness of the referenced key, deferrability, update action, delete action, tenant scope, archival rules, and owning service. Add cardinality, such as one customer to many orders or one user to at most one active profile. Include relationships enforced only in code or pipelines.

Ask what must happen when the parent changes. RESTRICT or NO ACTION preserves the child by rejecting the parent operation. CASCADE propagates deletion or key update. SET NULL removes the reference, while SET DEFAULT substitutes a defined value. The correct choice comes from lifecycle and retention requirements. QA validates the choice as well as its implementation.

Do not treat a constraint name as the specification. Read migration definitions, data model documentation, service rules, and retention policy. Compare them with the deployed catalog because schema drift is possible. The SQL joins for testers guide is a helpful foundation for the anti-joins and cardinality checks used throughout this strategy.

2. Discover the Deployed Schema and Constraint Coverage

A test plan based on an old diagram can miss the most important relationships. Inspect the deployed schema in the target environment. In PostgreSQL, information_schema and system catalogs expose foreign keys, columns, and actions. The following query lists foreign key columns and their update and delete rules. It works for single and composite relationships, producing one row per paired column.

SELECT
  tc.constraint_schema,
  tc.constraint_name,
  tc.table_schema AS child_schema,
  tc.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_schema AS parent_schema,
  ccu.table_name AS parent_table,
  ccu.column_name AS parent_column,
  rc.update_rule,
  rc.delete_rule
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
  ON kcu.constraint_catalog = tc.constraint_catalog
 AND kcu.constraint_schema = tc.constraint_schema
 AND kcu.constraint_name = tc.constraint_name
JOIN information_schema.referential_constraints AS rc
  ON rc.constraint_catalog = tc.constraint_catalog
 AND rc.constraint_schema = tc.constraint_schema
 AND rc.constraint_name = tc.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
  ON ccu.constraint_catalog = rc.unique_constraint_catalog
 AND ccu.constraint_schema = rc.unique_constraint_schema
 AND ccu.constraint_name = rc.unique_constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
ORDER BY child_schema, child_table, tc.constraint_name, kcu.ordinal_position;

Review gaps deliberately. A column named customer_id without a foreign key might be intentional because the parent lives in another service, or it might be schema drift. A physical constraint might exist but be marked not valid after a migration, leaving historical rows unchecked. Partitioned tables, replicas, warehouses, document stores, and event payloads may enforce relationships differently.

Compare types and collations on both sides. Look for integer width changes, string normalization, signedness in other database engines, and nullable child columns that conflict with a required business relation. Confirm the referenced columns are primary or unique under the intended tenant scope. A globally unique ID and a tenant-local number represent different guarantees.

Turn the inventory into coverage: each relationship needs create, update, delete, transaction, and repair evidence appropriate to its risk. Prioritize money, identity, authorization, inventory, and audit data.

3. Create a Minimal Schema That Exposes Real Edge Cases

Deterministic fixtures make constraint behavior visible. The PostgreSQL schema below models tenants, customers, orders, and order items. It uses a composite customer key so an order cannot point to a customer in another tenant, restricts customer deletion while orders exist, cascades order deletion to line items, and checks positive quantities. Run it in a disposable database or schema.

BEGIN;

CREATE TEMP TABLE tenants (
  tenant_id integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TEMP TABLE customers (
  tenant_id integer NOT NULL REFERENCES tenants(tenant_id) ON DELETE CASCADE,
  customer_id integer NOT NULL,
  email text NOT NULL,
  PRIMARY KEY (tenant_id, customer_id)
);

CREATE TEMP TABLE orders (
  order_id integer PRIMARY KEY,
  tenant_id integer NOT NULL,
  customer_id integer NOT NULL,
  status text NOT NULL,
  CONSTRAINT orders_customer_fk
    FOREIGN KEY (tenant_id, customer_id)
    REFERENCES customers (tenant_id, customer_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

CREATE TEMP TABLE order_items (
  order_id integer NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
  line_no integer NOT NULL,
  sku text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, line_no)
);

INSERT INTO tenants VALUES (10, 'North'), (20, 'South');
INSERT INTO customers VALUES
  (10, 100, 'north@example.test'),
  (20, 100, 'south@example.test');
INSERT INTO orders VALUES (1000, 10, 100, 'OPEN');
INSERT INTO order_items VALUES (1000, 1, 'SKU-1', 2);

ROLLBACK;

This model creates useful tests. Customer number 100 exists in both tenants, so a test that checks customer_id alone is insufficient. Deleting order 1000 should remove its line, while deleting the customer first should fail. Updating the composite customer key should propagate to the order as one tuple.

Use run-specific key ranges or temporary schemas to isolate parallel tests. Keep cleanup consistent with the behavior under test. If the test validates ON DELETE CASCADE, do not use cascade as an invisible generic teardown and then claim the feature passed. Assert affected rows first, preserve evidence, and clean any remaining fixtures afterward.

4. Test Inserts and Updates at Every Relationship Boundary

For insertion, cover a valid parent, missing parent, null reference, wrong tenant tuple, boundary key values, duplicate child identity, and type or format validation before the database call. A nullable relationship needs both an accepted null case and a later transition from null to a valid parent. A required relationship must reject null at the database and present an understandable application error.

Composite keys need special attention. Create (tenant_id=10, customer_id=100) and (tenant_id=20, customer_id=200), then attempt (10, 200). Both components exist somewhere, but the tuple does not. The operation must fail. This catches application code that validates columns independently.

Update tests work in both directions. Change a child reference from parent A to valid parent B, from A to missing parent, and from a value to null where allowed. Update a referenced parent key and validate CASCADE, RESTRICT, or NO ACTION exactly. Many applications use immutable surrogate keys, but a migration or administrative repair can still exercise this path.

For negative SQL tests, use a transaction and a savepoint so an expected constraint error does not abort the whole script. In psql, error handling and test frameworks can assert the SQLSTATE. PostgreSQL uses foreign key violation SQLSTATE 23503 and not-null violation 23502. At the API layer, avoid exposing raw database errors or constraint names. Map expected conflicts or validation failures to the documented safe response.

Always check postconditions. After a rejected insert, query by the attempted identifier and prove no partial child, audit record, inventory decrement, or emitted outbox event was committed. After a valid reassignment, prove old and new parents show correct cardinality and no cached representation remains stale.

5. Verify Delete Rules and Lifecycle Outcomes

Delete behavior carries the highest data-loss risk. Test the configured action from the parent and observe every direct and transitive child. A cascade from tenant to customer and then to another entity may remove far more rows than the first relationship suggests. Draw the dependency path before executing the case, seed uniquely identifiable records, and run in a disposable environment.

Use a count ledger: record expected rows in each affected table before deletion, perform the operation, then compare actual inserted, deleted, nulled, and retained rows. Include audit, outbox, search-index queue, attachment metadata, and derived tables if they are part of the transaction or eventual workflow. For eventual consumers, poll a business-visible condition with a timeout rather than sleeping for a fixed interval.

RESTRICT and NO ACTION may differ in timing and deferrability depending on the database. Verify whether the error occurs at the statement or commit boundary. Confirm the entire business transaction rolls back and the application produces a safe conflict message. Then remove or reassign children through the supported workflow and prove the parent can be deleted.

For SET NULL, verify the child column is actually nullable, the relationship disappears, the UI can render an absent parent, and reporting does not silently drop the child through an inner join. For SET DEFAULT, prove the default parent exists and is appropriate for every tenant. A global placeholder can violate ownership even while satisfying a physical constraint.

Restores deserve inverse coverage. If an archived parent is restored, determine whether children restore automatically, remain archived, or require review. Test repeated delete and restore commands for idempotency and audit clarity.

6. Detect Orphans, Cardinality Violations, and Scope Leaks

Independent validation queries are essential after migrations, imports, bulk jobs, and recovery. For a simple relationship, a left anti-join finds children whose nonnull reference has no parent:

SELECT o.order_id, o.tenant_id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c
  ON c.tenant_id = o.tenant_id
 AND c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

The expected result is zero rows unless approved historical exceptions are represented explicitly. Do not use NOT IN carelessly when nulls are possible, because SQL three-valued logic can hide results. NOT EXISTS is another reliable form. Scope every query correctly, especially when the same local identifier can occur in multiple tenants.

Physical integrity does not prove cardinality. To find users with more than one active profile when the business allows at most one, group by the parent and apply HAVING COUNT(*) > 1. To find required parents with no child, use a parent-to-child anti-join. Compare totals before and after a bulk operation and reconcile counts by status rather than relying on one grand total.

Check semantic orphans too. A child may reference a row that exists but is deleted, expired, disabled, belongs to another ownership boundary, or is not effective at the child's event time. Encode these conditions in validation queries based on the business rules. Store approved exceptions in a controlled table with reason, owner, and expiry rather than excluding unexplained IDs in test code.

The SQL GROUP BY and HAVING guide provides useful aggregation patterns for duplicate and cardinality checks. Save integrity queries as versioned operational checks, parameterize their scope, and make nonzero results visible without dumping sensitive row content into CI logs.

7. Testing Referential Integrity Under Concurrency

Application code often performs SELECT parent, then INSERT child. Between those statements, another transaction can delete the parent. Without a database constraint or correct locking and isolation, both application checks can appear valid while an orphan commits. Concurrency testing turns that timing window into a deliberate case.

Use two independent database sessions. Session A begins and attempts a parent delete while session B attempts a child insert, varying which statement starts first. Observe blocking, deadlock handling, commit results, SQLSTATE, and final rows. The acceptable outcome is not necessarily a particular winner, but the committed database must remain valid. Repeat for parent-key update and child reassignment.

Test transaction boundaries in the service too. If creating an order also reserves inventory and writes an outbox event, inject a failure after each step in a controlled environment. Prove all atomic database changes commit together or roll back together. For cross-service workflows that use eventual consistency, physical foreign keys may be impossible. Validate the saga contract: retry, idempotency, compensation, dead-letter handling, reconciliation, and the maximum permitted inconsistency window.

Deferrable constraints require commit-level tests. A transaction may temporarily violate a relationship while reorganizing keys and become valid before commit. Verify the intended constraint mode and ensure a still-invalid transaction fails at commit. Test savepoints and retries, because a framework may catch a statement exception but accidentally commit unrelated work.

Do not use arbitrary sleeps to coordinate sessions. Use latches in test code, lock observation, controllable hooks, or database synchronization so the interleaving is repeatable. After each run, execute independent orphan and cardinality queries.

8. Cover Soft Deletes, History, and Non-FK References

Soft deletion changes availability without physically removing the parent, so a foreign key remains satisfied even when the business relation becomes invalid. Define whether active children may reference a soft-deleted parent, whether new children can be created, whether existing children remain visible, and how restore works. Test service queries, UI selectors, exports, and background jobs, not only database rows.

Temporal models add effective intervals. A price, policy, or organization membership may be valid when an event occurred but inactive now. Verify references at lower and upper time boundaries, overlapping versions, time zones, and corrections to historical intervals. The right rule may be to retain the exact historical version rather than repointing to the current one.

Polymorphic associations such as (target_type, target_id) rarely have a normal foreign key across several tables. Test every allowed target type, a valid ID of the wrong type, unknown type, missing target, deletion of each target, and authorization scope. A shared registry table can support stronger constraints, but QA must validate the deployed design rather than assume it.

External references need contract and reconciliation tests. An invoice may store a payment-provider customer ID whose parent is outside the database. Use sandbox providers or service virtualization to cover valid, missing, deleted, duplicated, and delayed objects. Verify retry and repair jobs without creating external production data.

Event payloads also carry references. Consumers may receive a child event before the parent due to partitioning or replay. Test buffering, retry, idempotency, tombstones, schema evolution, and dead-letter recovery. Integrity in a distributed system is a lifecycle property with measurable convergence, not a single SQL constraint.

9. Test Migrations, Imports, Backfills, and Restores

Relationship defects often enter through privileged data paths that bypass ordinary APIs. Every migration or bulk import should define preconditions, transformation rules, constraint state, validation queries, reconciliation totals, rollback, restart behavior, and ownership of rejected rows. Test with realistic volume and deliberately malformed relationships.

Before migration, profile nulls, duplicates, missing parents, cross-tenant tuples, and values outside the new key range. Decide how each class is repaired or rejected. During migration, verify ordering so parents exist before children where required. If constraints are temporarily disabled or added without validating historical rows, record that risk and require an explicit validation step before release.

After migration, run anti-joins, cardinality queries, aggregate reconciliation, sampled business workflows, and catalog checks proving constraints are enabled and valid. Compare by meaningful partitions such as tenant, date, and status. A matching grand total can conceal equal numbers of missing and duplicated rows. The testing data migrations guide gives a broader cutover framework.

Test restartability by stopping after a controlled batch, rerunning the job, and verifying no duplicate relationships or skipped rows. Test rollback from each approved point. For backfills, ensure newly arriving writes are not missed between the initial scan and catch-up phase. Preserve source-to-target lineage through protected identifiers.

Backup restore testing must include relationship checks, not just row counts. Restore all dependent databases or snapshots to a consistent point, validate sequences and key generators, run constraint checks, and execute representative create, update, and delete workflows. A database that opens successfully can still contain logically inconsistent snapshots across services.

10. Validate API Behavior, Automation, and Evidence

Service tests should prove that database guarantees appear as stable user-facing behavior. Create a child with a valid parent and verify the response and persisted relation. Try a missing, unauthorized, deleted, or wrong-tenant parent and assert the documented 4xx status and safe machine-readable error. Then query the database or a trusted read API to prove no partial state exists.

Avoid over-mocking repositories in relationship tests. A mock that always returns parent exists cannot exercise the database race, constraint mapping, or transaction rollback. Keep fast unit tests for validation branches, then add integration tests against the real database engine and migrations used by production. Containerized or ephemeral database instances make exact schema tests practical.

Build reusable assertions that accept table and key metadata, but keep expected business rules explicit. A generic anti-join helper should report safe identifiers and counts, not silently delete or repair failures. Store seed, operation, expected affected rows, actual affected rows, SQLSTATE or API error code, transaction ID, and postcondition result in the report.

Run a small suite on each schema or service change: valid create, invalid parent, wrong scope, delete action, rollback, and orphan query. Schedule deeper concurrency, migration rehearsal, restore, repair, and high-volume validation. Alert on unexpected integrity errors in production, but do not expose raw rows in telemetry.

Finally, review database permissions. Application roles should not disable triggers, bypass row security, or alter constraints. Administrative jobs that can bypass safeguards need their own tests, approvals, audit trail, and post-run integrity gate.

Interview Questions and Answers

Q: What is referential integrity, and how would you test it?

Referential integrity ensures a child reference points to an allowed parent or follows a defined nullable exception. I inventory relationships and lifecycle rules, test valid and invalid CRUD operations, verify update and delete actions, and run independent orphan queries. I add concurrency, migration, soft-delete, scope, and recovery coverage for high-risk relationships.

Q: Is a foreign key enough to guarantee business integrity?

No. It proves that referenced key values exist under the physical constraint, but not that the parent is active, belongs to the correct business scope unless scope is in the key, or is valid at a particular time. Soft deletes, polymorphic links, external objects, and distributed workflows need additional controls and tests.

Q: How do you test ON DELETE CASCADE safely?

I use a disposable isolated dataset, map the full dependency path, and record expected affected rows per table. After deleting the parent, I verify exact direct and transitive deletions plus audit and event outcomes. I also prove unrelated rows remain and that retries are idempotent.

Q: How can a race condition create an orphan despite an application precheck?

One transaction can confirm the parent exists while another deletes it before the child insert commits. I reproduce this with two synchronized sessions and verify the database constraint or transaction strategy prevents both incompatible outcomes from committing. The final anti-join must return no unexpected rows.

Q: What query finds orphan child rows?

A left join from child to parent with a WHERE clause selecting a null parent key is a common anti-join. NOT EXISTS is also reliable. The join must include the complete key and business scope, and nullable references should be excluded only when null is an allowed state.

Q: How do you test a composite foreign key?

I cover a fully valid tuple, a completely missing tuple, null combinations as allowed by the database, and a mismatched tuple assembled from components that exist under different parents. I then update each component and the tuple together to verify the configured action and tenant scope.

Q: What should be tested when constraints are disabled for a migration?

I validate preexisting data, import ordering, rejected-row handling, restartability, and the exact period without enforcement. Before release, I run anti-joins and cardinality reconciliation and prove constraints are re-enabled and validated for historical rows. I also rehearse rollback and concurrent-write catch-up.

Q: How would you test referential integrity across microservices?

I treat it as an eventual-consistency contract. I test event ordering, idempotent retry, missing parent handling, tombstones, compensation, dead-letter recovery, reconciliation, and the maximum allowed inconsistency period. Sandbox APIs or service virtualization cover external parent lifecycle without touching production.

Common Mistakes

• Testing only successful inserts. Missing-parent, wrong-scope, null, update, and delete paths define the real integrity boundary.
• Assuming a column ending in _id has a deployed and valid foreign key. Inspect the actual catalog and migrations.
• Joining on only part of a composite key. This can make a cross-tenant reference look valid.
• Treating a zero orphan count as proof of every rule. Cardinality, state, temporal validity, and ownership need separate checks.
• Using NOT IN without considering null semantics. A null in the subquery can produce surprising results.
• Testing cascade cleanup without mapping transitive data loss. A parent delete may reach audit or history tables unintentionally.
• Validating only through mocks. Repository mocks cannot prove database atomicity, constraint timing, or rollback.
• Disabling constraints for imports without a required post-load validation gate. Successful job completion does not prove valid relationships.
• Ignoring concurrent create and delete operations. Application-level existence checks have a race window.
• Reusing generic teardown cascades without asserting their outcome. Cleanup can hide the behavior under test.
• Logging complete orphan rows in CI. Report protected identifiers and counts, with sensitive details restricted to approved diagnostics.

Conclusion

Testing referential integrity is a lifecycle proof across database structure, service behavior, concurrency, privileged data paths, and recovery. Start from the deployed relationship inventory, force every CRUD boundary with deterministic fixtures, and verify the final state using independent orphan, cardinality, and scope checks.

For your next test, choose one high-risk parent-child pair and write four cases: valid create, missing parent, parent delete, and concurrent delete versus child insert. Add a postcondition query after each case. That compact set will reveal whether the relationship is protected only on the happy path or throughout its real lifecycle.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Testing database constraints (2026)
• Accessibility testing checklist (2026)
• Accessibility testing with Cypress (2026)
• Accessibility testing with Playwright (2026)
• Agentic testing with tool calling (2026)
• Agile testing quadrants (2026)