Last updated: 2026-05-02

Loose Foreign Keys

Declare logical column-to-column references that the database doesn't enforce, and Jam SQL Studio will treat them like real foreign keys — clickable cells, lookup pickers, inbound related data, and Schema Overview / Dependency Viewer edges all work without altering the schema. Declarations live in a per-database MetaInfo file you can export, import, and share.

What is a loose foreign key?

A loose foreign key (or loose relationship) is a user-declared reference between a column in one table and a column in another, persisted by Jam SQL Studio rather than by a FOREIGN KEY constraint in the database. The schema stays untouched; only Jam SQL Studio's per-database MetaInfo file knows about the link.

Once declared, a loose FK behaves like a real one across the app:

  • Cell values become clickable and open the FK popover
  • The Table Explorer filter row gets the lookup picker for that column
  • The Row Details “Related data” tab lists inbound rows from tables that loosely reference the row you're viewing
  • Schema Overview draws the relationship as a dashed blue edge labelled “Loose relationship”
  • Dependency Viewer marks the connected nodes with a “Loose” badge and dashes the edge

When to use them

  • Legacy schemas where FK constraints were never added
  • Denormalised reporting / ETL staging tables that intentionally skip constraints for load performance
  • Cross-schema, cross-database, or polymorphic references the database can't model with a single FK
  • Read-only access where you can't issue DDL but still want to navigate the data

Declaring a loose foreign key

  1. Open Table Explorer on the table whose column you want to link
  2. Open the filter panel and add a row for the source column
  3. In the operator dropdown, pick lookup — it's at the bottom of the list for non-FK columns
  4. The target picker dialog opens with a suggestion already filled in. Jam SQL Studio strips common suffixes (_id, _uuid, _fk, Id, Uuid) from the column name and matches against schema/table/column names — including singular/plural variants and prefix overlaps (t_, tbl_, app_). For the column the picker focuses, it prefers the target table's primary key, then a column named id, then any column whose type matches the source column's.
  5. Confirm the suggestion, or pick a different schema, table, and column from the dropdowns
  6. Click Save. The relationship is persisted, the filter row immediately switches to the same lookup picker used by real foreign keys, and every other surface (Query Editor results, Schema Overview, Dependency Viewer, Row Details, Data Profiling) starts treating the column as an FK.

The picker remains available — you can reopen the dropdown later and switch the operator back to =, IN, IS NULL, or any other operator if you want to type a raw value. lookup is a convenient default once a target is declared, not a lock-in.

Visual cue: loose FK vs real FK

Loose FKs are styled distinctly so you always know which references are enforced by the database and which are user-declared:

  • Real FKs — solid blue link with the external-link icon. Column header FK badge in blue.
  • Loose FKs — teal link with a dashed underline and the chain (Link2) icon. Column header FK badge in teal italic.
  • The cell popover header reads “Related row from (loose)” for loose relationships, plus a footer with Edit (repoint the target) and Clear (remove the declaration) actions.
  • Schema Overview edges are dashed blue and labelled “Loose relationship”; Dependency Viewer marks connected nodes with a “Loose” badge.

Editing or removing a loose FK

You can manage a single relationship inline:

  • From a cell. Click the cell to open the FK popover. The footer shows Loose relation with Edit (pencil icon, reopens the target picker) and Clear (unlink icon, removes the declaration) buttons.
  • From the filter row that uses it. An edit pencil reopens the target picker so you can repoint to a different table or column; an X removes the declaration.

To see and manage every loose relationship for the current database in one place, use the MetaInfo manager — see below.

MetaInfo: where loose FKs live, and how to share them

Loose foreign keys are persisted in a per-database MetaInfo file — Jam SQL Studio's home for user-owned database metadata that the schema itself doesn't carry.

Storage location

One JSON file per (connection, database) pair, in your operating system's user-data directory:

  • macOS: ~/Library/Application Support/jam-sql-studio/metainfo/<connection>/<database>.json
  • Windows: %APPDATA%\jam-sql-studio\metainfo\<connection>\<database>.json
  • Linux: ~/.config/jam-sql-studio/metainfo/<connection>/<database>.json

The file is versioned and written atomically (temp file + rename), so a crash mid-write can never leave a half-written JSON. Every read validates the version. The same file is consumed by Table Explorer, Query Editor, Schema Overview, Dependency Viewer, Data Profiling, and the Row Details “Related data” tab — declare a loose FK once and every surface picks it up.

Opening the MetaInfo manager

You can open the MetaInfo manager dialog from two places:

  • The Link2 icon in the Table Explorer toolbar
  • Right-click a database in the Object Explorer sidebar and pick Manage loose relationships…

Both paths open the same dialog with three things:

  • List of declarations — every loose relationship for the current connection + database, with the source column, target column, and a Remove action on each row
  • Export — writes the entire MetaInfo file as JSON to a file on disk
  • Import — reads a JSON file and merges or replaces the local MetaInfo

Exporting MetaInfo

  1. Open the MetaInfo manager from Table Explorer's toolbar (or right-click a database in the Object Explorer → Manage loose relationships…)
  2. Click Export
  3. Pick a location for the JSON file (default filename: metainfo-<connection>-<database>.json)

The exported file contains the full DatabaseMetaInfo document — version field, connection / database identifiers, last-updated timestamp, and the full looseRelationships array. It's plain JSON; you can commit it to a repository, attach it to a ticket, or send it to a teammate.

Importing MetaInfo

Importing offers two modes:

  • Merge (recommended for sharing) — appends every imported declaration whose source column (schema, table, column) isn't already declared locally. Local entries always win on conflict, so you never lose your own work.
  • Replace — overwrites the entire local MetaInfo file with the imported document. Use this only when you explicitly want the imported version to be authoritative.
  1. Open the MetaInfo manager from Table Explorer's toolbar (or right-click a database in the Object Explorer → Manage loose relationships…)
  2. Click Import and pick the JSON file
  3. Choose Merge or Replace
  4. The list refreshes immediately, and every other workspace tab that depends on MetaInfo (Query Editor, Schema Overview, Dependency Viewer, etc.) picks up the change on its next read.

Import validates the version and shape of the JSON. Files that don't match the current MetaInfo schema are rejected with a clear error rather than silently truncating your data.

Sharing tips

  • Per-database scope. One file per (connection, database). If you maintain loose FKs for several databases on the same server, export each one separately.
  • Connection identity. The exported file embeds the source connection id; on import, Jam SQL Studio rewrites it to match the local target connection so a teammate can import your file against their own connection without renaming anything.
  • Version control friendly. The JSON is pretty-printed and stable to diff. Some teams check it into the same repo as the database migrations, so anyone cloning the project gets the curated set of loose FKs out of the box.

Where loose FKs show up

SurfaceBehaviour
Table Explorer column headerFK badge rendered in teal italic (vs. blue for real FKs)
Table Explorer cell valueTeal text, dashed underline, chain icon — clickable, opens the FK popover
Table Explorer filter rowlookup operator opens the same picker used by real FKs (search + per-column filters + drill-down)
Query Editor result gridLoose-FK columns are detected automatically when the query selects from a table with declared loose FKs — cells render as teal clickable links
Row Details — Related data tabLists inbound rows from tables that loosely reference the current row, marked with a “Loose” badge
Schema OverviewDashed blue edges labelled “Loose relationship”
Dependency ViewerConnected nodes carry a “Loose” badge; edges drawn dashed blue
Data Profiling filterslookup operator works the same as in Table Explorer — including declaring a new loose FK on the fly

Limitations

  • One declaration per source column. A given (schema, table, column) can only point at one target. Re-declaring overwrites.
  • Local to the machine until exported. Loose FKs aren't stored in the database, so colleagues won't see them unless you Export and they Import.
  • No referential enforcement. Loose FKs are a navigation aid, not a constraint — the database will still let you insert rows that point at non-existent target rows. If you need enforcement, add a real FOREIGN KEY constraint with Table Designer.