Skip to main content
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 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 to resolve a navigation link in REST or MCP.

​
REST and MCP

Use Create foreign key or rootset_create_foreign_key with stable field IDs from the table schemas:
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 and the table schema return the active mappings. Invalid references return FOREIGN_KEY_CONFLICT; activation failures include a bounded list of affected row IDs.