> ## Documentation Index
> Fetch the complete documentation index at: https://www.rootset.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Table relations

> Validate values against a unique key in another table in the same database.

Open **Settings → Table relations** in the table that stores the reference, then select **Create table relation**.

1. Choose the referenced table in the same database.
2. Choose one of its active unique rules that applies to **All rows**.
3. Map each referenced field to a local field with the same type.

For example, make `Airlines.ALLIANCE_CODE` reference the unique `Alliances.CODE` key. Every non-NULL alliance code in Airlines must then exist in Alliances. You can also map a combination of up to eight scalar fields. Text matching preserves case and whitespace, including for wide keys. JSON fields and conditional unique rules cannot be reference targets.

## Validation and changes

Rootset validates existing rows before activating the table relation. A missing reference fails the operation without changing rows, activating a rule, or incrementing the schema version. Correct the reported rows and submit a new request.

After activation, PostgreSQL enforces references for UI, REST, MCP, batch, import and repair writes. If any local field in a composite reference is NULL, that row does not require a match. Required fields still reject NULL through their field definition.

Referenced values cannot be changed or deleted while rows use them. Update or remove those references first. Rootset never automatically cascades changes or deletes. Batch mutations run in order and roll back together on a violation.

Renaming a field or table preserves the link. Remove dependent table relations before changing linked field types, deleting linked fields or tables, or removing the referenced unique rule. Deleting the whole database removes its internal links with its data. A table can reference itself.

Removing a table relation leaves existing records in place and stops validation for future writes. A table supports up to sixteen table relations.

## Choose a linked record

The record drawer provides a searchable picker for each table relation. Double-click a linked spreadsheet cell, or select it and press F2, to open the record drawer at that relationship. Search by a label or a typed key. The picker shows the display label alongside the actual stored key; choosing a composite key updates all its local fields together.

Open the relationship’s options menu to choose a **Display field**. This personal choice is remembered across sessions and devices without changing record values or another person’s view. Rootset uses a text field as the initial label when one is available. Record reads and searches require current access to the referenced table. Searches and alphabetical result pages contain at most 50 records, with Previous and Next controls; selecting a NULL label does not turn a non-NULL key into NULL.

Choose **NULL** to clear the optional fields of a link. Required fields retain their existing values. **Enter keys manually** exposes the original typed field controls, including defaults and generated IDs when creating a record. All selections and manual values still pass the table’s validation on save. A failed constraint keeps the draft and focuses the affected fields.

The `updateForeignKeyDisplayField` operation changes a personal label atomically and validates that the display field belongs to the referenced table. Passing `null` restores automatic label choice. Ordinary `updateViewPreferences` saves preserve these label choices so a column resize cannot overwrite them.

## Relationship APIs

Use [Get row relationships](/api-reference/getRowRelationships) to read referencing tables, their foreign-key fields and exact matching record counts in REST or MCP. Counts come from the reference registry and use saved values. Supply the parent record's `expectedVersion` when counts must match a particular revision.

Composite keys retain their types and exact text, including leading zeros, `0` and `false`. A parent with a NULL key component has no reference link. Results include each table's maintenance status and require current row and schema read access. Use the returned cursor to read additional relationships.

Links identify the relationship and parent record without embedding long key values in the URL. The destination resolves the current saved key before loading any rows. Deleted rules, missing records, lost access and incomplete keys show an unavailable state; they never silently open the complete table. You can explicitly open the table without the relationship filter when needed.

Use [Get related row filter](/api-reference/getRelatedRowFilter) to resolve a navigation link in REST or MCP.

## REST and MCP

Use [Create foreign key](/api-reference/createForeignKey) or `rootset_create_foreign_key` with stable field IDs from the table schemas:

```json theme={"system"}
{
  "fieldIds": ["01ARZ3NDEKTSV4RRFFQ69G5FAV"],
  "referencedTableId": "01ARZ3NDEKTSV4RRFFQ69G5FAW",
  "referencedRuleId": "01ARZ3NDEKTSV4RRFFQ69G5FAX",
  "expectedSchemaVersion": 3,
  "expectedReferencedSchemaVersion": 2
}
```

Supply both schema versions so a concurrent schema change requires a reload. Order local `fieldIds` to match the referenced unique rule's field order. REST requests require an `Idempotency-Key` header; MCP calls also include the organisation, database and local table IDs, plus `idempotencyKey`.

Creation returns an accepted job. Follow `getJob` or `rootset_get_job` until it succeeds. [List foreign keys](/api-reference/listForeignKeys) and the table schema return the active mappings. Invalid references return `FOREIGN_KEY_CONFLICT`; activation failures include a bounded list of affected row IDs.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.