Referential Integrity for AI-Agent Datasets

Referential integrity means every stored reference points to a valid target record, or is deliberately blank. In an AI-agent workflow, protect it before a child write, after an uncertain response, before renaming or deleting a target, and during periodic orphan checks. The agent should never repair a broken link by guessing.

For example, if Messages.person_id points to People.person_id, every non-blank person ID in a message must identify a real person row. A missing target creates an orphan record: the message claims a relationship that the system cannot resolve.

This guide uses a five-step VALID integrity contract:

1. Verify the target exists.
2. Add the parent first when creation is authorized.
3. Link the exact stable index value.
4. Inspect the stored child and resolved target after the write.
5. Delete or rename targets deliberately, with dependent rows handled first.

In this guide

• What is referential integrity?
• Why is it different for AI agents?
• The VALID integrity contract
• How Rowset enforces relationships
• How to enable enforcement on existing data
• Which delete behavior should you choose?
• How to recover from a violation
• Referential integrity FAQ

What is referential integrity?

Referential integrity is the rule that every reference between records remains valid. In a relational database, a foreign key in a child table must match a primary or unique key in a parent table. PostgreSQL defines a foreign-key constraint as requiring values in one column or group of columns to match values in a row of another table (PostgreSQL constraints, checked August 2026).

Three mutations can break that rule:

• inserting or updating a child with a target ID that does not exist
• deleting a parent that still has dependent children
• changing a parent's referenced key without updating its children

The terms describe roles, not importance. A People row is the parent of a Message row when the message stores its person_id. The same People row can be a child in another relationship, such as People.account_id -> Accounts.account_id.

Referential integrity is narrower than data integrity. Data integrity also covers valid types, ranges, required fields, uniqueness, and business rules. Referential integrity deals specifically with whether links between records point somewhere real.

The existing guide to modeling relationships between agent-managed datasets helps decide when two entities deserve separate datasets and which stable index should connect them. This guide starts after that design decision. Its job is to keep the chosen link valid while an agent changes data.

Why does referential integrity need an agent workflow?

A database constraint can reject an invalid write, but an agent still has to decide what to do next. That decision is where unsafe repairs appear.

Suppose an agent tries to create a message for P-404 and receives "target row not found." Several responses are possible:

• stop and report the missing person
• search for the correct existing person
• create a new person, if the user and dataset instructions authorize it
• leave the relationship blank and route the message to review, if blanks are allowed
• invent a plausible person ID and retry

Only the last response is always wrong. The other choices depend on workflow authority and the meaning of the missing target. A constraint detects the broken link; durable instructions define the safe recovery path.

Agent tool calls also create an ordering problem. Creating a person and creating a message may be two separate MCP or REST operations. The first call can succeed while its response is lost, or the second can fail after the parent exists. Treating the pair as if it were one atomic operation can produce duplicates on retry.

Use a stable index and idempotent update pattern: read by the business key after an uncertain response, reuse the confirmed target, then write the child. Do not mint a second parent merely because the agent did not receive the first response.

Use the VALID integrity contract

VALID turns referential integrity from a database error into an agent operating procedure.

1. Verify the target

Resolve the relationship before writing the child. Search when the target dataset is unknown, then use an exact index lookup once you know the dataset and key.

For Messages.person_id = P-17, verify that People contains P-17. A fuzzy match on a display name is evidence for review, not permission to substitute a different ID. If several source systems use different IDs, translate them through an approved crosswalk table first.

2. Add the parent first

Create the parent only when the workflow authorizes creation and search has ruled out an existing record. Give it a stable index before any child refers to it.

Parent-first ordering makes the invariant easy to state: a child write never introduces a target that the agent has not already read or created. It also keeps a failure on the child side from leaving a broken reference.

3. Link the exact stable index

Store the target's index value, not a label that happens to look unique. Names, titles, and mutable emails are weak relationship handles. Values such as person_id, sku, ticket_id, and content_id make exact lookup and enforcement predictable.

If the target is identified by several fields, encode the full identity deliberately. The composite-primary-key guide shows how to carry a scoped tuple through a single-index agent interface without ambiguous concatenation.

4. Inspect the stored result

Read the child after a successful or uncertain write, then resolve its relationship. Confirm two facts separately:

1. the child contains the intended target index value
2. that value resolves to the intended target row

This catches serialization mistakes, trimmed or transformed values, writes to the wrong dataset, and timeouts where the mutation succeeded but the response did not arrive.

5. Delete or rename deliberately

Before deleting a parent or changing its index, list or count incoming references. Decide whether the children should be deleted, reassigned, detached, or preserved with the operation blocked.

For agent-managed operational data, blocking is the safest default. Automatic cascading deletes can turn one mistaken tool call into the removal of an entire dependent record set. Use a cascade only when the child has no meaning outside the parent and the destructive scope is explicit.

How does Rowset enforce referential integrity?

Rowset relationships connect a source dataset column to the index column of a target dataset. A relationship can say Messages.person_id -> People.person_id, while both datasets remain available through authenticated MCP tools and the Dataset API.

With relationship enforcement enabled, Rowset:

• accepts a non-blank source value only when the target index exists
• allows a blank value for a relationship that is not known yet
• rejects enabling enforcement while existing non-blank values lack targets
• blocks deletion of a referenced target row
• blocks changing a target index while enforced source rows still reference it

These rules protect target existence and identity. They do not decide whether a missing person should be created, whether an optional link may remain blank, or who may approve a destructive cleanup. Put those decisions in dataset instructions and review policy.

Inspect the dataset with get_dataset before row work so the agent sees outgoing and incoming relationship summaries. Use resolve_dataset_relationship when you need the target row behind a source value. The link-datasets reference lists the equivalent REST routes.

An instruction block can make the repair boundary explicit:

Messages.person_id points to People.person_id.
Resolve person_id before creating or updating a message.
If the target is missing, search People by exact external identifiers.
Do not create a new person or replace person_id from a name match without approval.
After a write, read the message and resolve the relationship.
Do not delete or rename a referenced People row until dependent messages are reviewed.

How do you enable enforcement on existing data?

Audit first. Turning on enforcement without checking existing values can fail immediately or hide how many exceptions need a real decision.

Use this sequence:

1. List distinct non-blank values in the proposed source column.
2. Compare them with exact index values in the target dataset.
3. Put missing values in a review queue; do not auto-create targets from display labels.
4. Correct confirmed mistakes, create authorized targets, or blank explicitly optional links.
5. Create the enforced relationship only after every remaining non-blank value resolves.
6. Run a final sample of relationship resolutions and record the audit date.

Keep source evidence for every correction. If P-071 becomes P-17, record why those values refer to the same person instead of silently rewriting the child. The AI-agent audit trail guide provides a durable mutation record for the actor, before/after values, reason, evidence, and result.

When messy source material must land before matching is complete, stage it outside the enforced dataset or start with enforcement disabled and an explicit review status. An unenforced relationship is a navigation hint, not proof that every target exists.

Which referential action should an agent workflow use?

SQL databases provide several actions for target updates and deletes. PostgreSQL and SQLite both document NO ACTION, RESTRICT, CASCADE, SET NULL, and SET DEFAULT for foreign keys (PostgreSQL, SQLite, checked August 2026).

PostgreSQL distinguishes RESTRICT from deferrable NO ACTION and advises choosing the delete behavior from the meaning of the related objects. If child rows are components that cannot exist independently, cascading may fit. If parent and child represent independent objects, blocking the delete is usually more appropriate.

Rowset's enforced relationships follow the conservative operational choice: referenced target deletion and index changes are blocked. If you intend to remove a target, update or delete its dependent rows explicitly first. This keeps each mutation visible to the agent and reviewable by a human.

What happens if referential integrity is violated?

An enforced system rejects the invalid mutation. An unenforced system can retain an orphan that fails to resolve later. In either case, use the evidence to repair the relationship. Clearing the error message alone is incomplete.

Use this recovery order:

1. Preserve the rejected payload or orphan row as evidence.
2. Inspect the source dataset, relationship definition, and target index column.
3. Search for the intended target using stable source identifiers.
4. Classify the cause: typo, wrong namespace, missing parent, retired target, or unauthorized creation.
5. Propose one explicit repair and require review when identity or deletion is uncertain.
6. Apply the change with an idempotent write.
7. Read the child and resolve the target again.

Do not make the error disappear by disabling enforcement permanently. If temporary unmatched rows are legitimate, model that state: allow blank links, use a staging dataset, or keep a review status that distinguishes unmatched from approved.

A practical checklist

Before an AI agent writes related records, confirm:

• the parent and child datasets have separate, useful jobs
• the target index is stable, unique, and visible to the agent
• the source column stores that exact index value
• dataset instructions define when target creation is authorized
• uncertain tool responses trigger read-back, not blind retry
• existing source values have been audited before enforcement
• target deletes and index changes are blocked or explicitly reviewed
• repairs preserve evidence and finish with relationship resolution

When these checks pass, referential integrity gives the agent a clear stop condition and gives reviewers a durable contract for every linked write. You can test the pattern with a private dataset through the Rowset quickstart and keep the hosted product after the trial on Rowset Pro.

Referential integrity FAQ

What is meant by referential integrity?

Referential integrity means every non-blank reference in a child record matches a valid key in its parent record. It prevents inserts, updates, key changes, or deletes from leaving links that point to missing data.

What is the difference between data integrity and referential integrity?

Data integrity covers the overall correctness of stored data, including types, required values, uniqueness, ranges, and business rules. Referential integrity is one part of data integrity. It focuses on keeping references between records valid.

Should an AI agent create a missing parent automatically?

Only when the user or dataset instructions authorize creation and an exact search confirms the parent does not already exist. A missing target may be a typo, wrong ID namespace, delayed import, or duplicate. The agent should not turn every lookup failure into a new record.

Are blank relationship values referential-integrity violations?

Not necessarily. Optional relationships may be blank by design. A required relationship should be enforced with both a valid target check and a required-value rule; target validation alone does not make a blank field invalid.

Is referential integrity the same as a foreign key?

No. Referential integrity is the property that references remain valid. A foreign-key constraint is a database mechanism for enforcing that property. Rowset relationships provide similar target-existence protection at the dataset and agent-tool layer.