Published: 2026-05-05

Loose Foreign Keys: Navigate Schemas the Database Doesn't Describe

Most real-world databases have relationships the schema doesn't describe. A customer_id column that points at customers.id with no FOREIGN KEY constraint to back it up. A parent_article_id on a denormalised reporting table. A tag_ids integer[] in Postgres where every element is meant to be a primary key in another table. To the SQL editor, those are just integers — you can't click them, can't drill in, can't see what they reference. Loose foreign keys close that gap.

What is a loose foreign key?

A loose foreign key — sometimes called a virtual or logical foreign key — is a user-declared reference between a column in one table and a column in another, persisted by the SQL tool rather than by a constraint in the database. The DDL stays untouched. Only the tool knows about the link, and it uses that knowledge to make the column behave like a real FK in the UI: clickable cells, lookup pickers, FK popovers, inbound “related data” tabs, schema-overview edges.

It's a navigation aid, not a constraint. The database will still happily let you insert a customer_id that points at no real customer. But the workflow — reading data, jumping from row to row, exploring relationships — works the same as if the constraint were there.

An idea borrowed from NoSQL

The relational world standardised on FOREIGN KEY in the 1980s. The concept of a reference that isn't enforced by the data store is much more recent — and it came from NoSQL.

• MongoDB has DBRefs — a documented convention for storing { "$ref": "users", "$id": ObjectId(…) } pointers between collections. Mongo doesn't enforce them; the application (or the GUI) is expected to dereference.
• Firestore exposes document references as a field type. They're not enforced — deleting the target leaves a dangling reference — but tools like the Firebase console render them as clickable links.
• DynamoDB takes the position even further: foreign keys are explicitly an application concern. Items in different tables reference each other by string ids, and your code is responsible for keeping them consistent.

The shared lesson: a reference that the storage layer doesn't enforce is still a reference. If you can describe it to the tool, the tool can give you the same browsing experience as a real FK. Loose foreign keys are that idea brought back into the relational world — not as a NoSQL substitute, but as a layer on top of SQL for the references the SQL itself doesn't carry.

When loose foreign keys are the right tool

Use a loose foreign key when there is a real relationship in your data, but the schema doesn't — or can't — express it.

• Legacy schemas without FK constraints. Old codebases where every reference is “just an integer” and adding constraints retroactively would break loaders nobody owns anymore.
• Data warehouses and ETL staging tables. Bulk-load pipelines often skip constraints intentionally so COPY / BULK INSERT stays fast. The relationships still exist in the data — they're just not declared.
• Cross-schema or cross-database references. A column in analytics.user_event that references app.user.id can't always be expressed as a real FK (cross-database FKs aren't supported on most engines).
• Polymorphic associations. A commentable_id column whose target depends on commentable_type. The relational model can't put a single FK on it, but you can declare a loose FK to whichever table you're currently exploring.
• Read-only access. You're a consumer of someone else's database. You can SELECT but you can't issue DDL. A loose FK lives in your tool, not in the database, so you don't need DDL rights to declare one.
• Array-typed reference columns. Postgres integer[], uuid[], text[] — columns that hold multiple references at once. SQL has no native FK on an array element. A loose FK gives you per-element navigation anyway.

When NOT to use them

Loose foreign keys are seductive. They're free, they're fast, and they make every table feel navigable. That's exactly why it's worth being clear on where they don't belong.

• When you need referential integrity. A loose FK does not prevent orphan rows. If a parent row gets deleted, the “child” that loosely referenced it now points at nothing — and the database won't tell you. If your application depends on the reference being valid, add a real FOREIGN KEY constraint.
• When you own the schema and could just add a real FK. A loose FK is a workaround for a missing constraint, not a replacement for one. If your migrations let you add the real thing, do that. Loose FKs are for situations where you can't.
• As a long-term substitute for fixing the schema. If everyone on the team has had to declare the same loose FK independently, the data model is telling you something. Get the constraint into the migration.
• When the “reference” isn't really a reference. Don't declare a loose FK from tax_rate_pct to countries.id just because both are integers. Loose FKs are for genuine logical references — not type-coincidences.

The mental model: a loose FK is documentation that's useful in your editor. It's not a contract.

How other SQL tools handle the concept

Loose / virtual foreign keys are not a universal feature. Some tools have them, some don't, and the ones that do call them different things:

Where DBeaver and DataGrip pioneered the idea on the desktop, the gap is on the rest of the market — especially the engine-specific tools (SSMS, pgAdmin, Workbench, SQL Developer), which all stop at real FK constraints. If your day involves any of the “when to use them” situations above and your tool doesn't have loose FKs, you're doing manual cross-referencing.

How Jam SQL Studio handles loose foreign keys

Jam SQL Studio's design goal for loose FKs is “indistinguishable from real FKs in the workflow, distinguishable at a glance in the UI.” Once you declare one, it shows up everywhere a real FK would — but it's styled differently so you always know which references are enforced and which are user-declared.

Declaring

Open Table Explorer on the table whose column you want to link. Add a filter row for the source column and pick the lookup operator. The target picker opens with a suggestion already filled in — Jam SQL Studio strips common suffixes (_id, _uuid, _fk, _ids, Id, Ids…) from the column name and matches against schema/table/column names, including singular/plural variants. For the column it focuses, it prefers the target table's primary key, then a column named id, then any column whose type matches the source.

You can confirm the suggestion or pick a different schema, table, and column. Save, and the relationship is persisted — in your MetaInfo file, not in the database.

Where loose FKs show up

• Table Explorer. Column header gets a teal italic FK badge (vs. solid blue for real FKs). Cells render as clickable teal links with a dashed underline and a chain icon.
• FK popover. Click any cell and the popover opens against the target row, with a header reading “Related row from (loose)” and a footer offering Edit (repoint the target) and Clear (remove the declaration).
• Query Editor result grid. If your query selects from a table with declared loose FKs, the result columns light up automatically — same teal links, same popover, no extra setup.
• Row Details — Related data tab. When viewing a row, Jam SQL Studio finds inbound rows from other tables that loosely reference it, and lists them under a “Loose” badge.
• Schema Overview. Loose relationships are drawn as dashed blue edges labelled “Loose relationship”, alongside the real FKs.
• Dependency Viewer. Connected nodes carry a “Loose” badge; edges drawn dashed blue.

Stored locally, exportable for the team

Loose FKs live in a per-database MetaInfo file under your user-data directory — one JSON file per (connection, database). The file is versioned, written atomically, and shared by every workspace tab that needs it.

From the MetaInfo manager dialog (Link2 icon in the Table Explorer toolbar, or right-click a database in the Object Explorer → Manage loose relationships…) you can Export the file as JSON and Import someone else's. Merge mode keys on the source column, so importing never overwrites a declaration you already have. Some teams check the JSON into their schema-migrations repo — anyone cloning the project gets the curated set out of the box.

New in 1.4.6: loose FKs on Postgres array columns

The standard relational FK lives on a single scalar column. But Postgres lets a column be an array — integer[], uuid[], text[]. A column called tag_ids integer[] on a posts table is a perfectly natural many-to-many shortcut, but no relational engine lets you put a real FOREIGN KEY on an array element. So in the typical SQL editor, an array column is an opaque blob — you read {1,2,3} and have to mentally cross-reference.

Jam SQL Studio 1.4.6 extends loose FKs to cover this case.

• Declare once. Add a filter row for the array column, pick lookup, and the target picker auto-suggests the singular target — tag_ids → tags.id, session_uuids → sessions.id. The plural-suffix heuristic handles both snake_case and camelCase (_ids, _uuids, Ids, Uuids).
• Per-element clickable pills. The cell renders as a row of teal pills, one per array element. Each pill is its own FK link — click any one to open the popover against that target row. Pills are capped at 8; overflow opens the expanded-text dialog with the full JSON.
• Inbound related data. On the target table (e.g. tags), the Row Details “Related data” tab finds every parent row whose array column contains the focused row's id, using <value> = ANY(<array_col>) semantics under the hood.
• One declaration, no schema change. The relationship is stored as a single LooseRelationship in MetaInfo, the same shape as scalar loose FKs. Postgres-only today — no other supported engine has native array columns.

This is the kind of feature where the alternative is “type out a dozen WHERE id = ANY(tag_ids) queries by hand.” Once it's wired in once, every cell, every row-detail tab, every dependency edge just works.

The takeaway

Real foreign keys are a contract: the database refuses to let the data drift. Loose foreign keys are documentation: you describe how the data fits together, and the tool gives you the same navigation experience without changing the schema. They're the right answer for legacy databases, ETL staging, polymorphic links, cross-database references, read-only access, and array-element references — and the wrong answer when you actually need the database to enforce integrity.

The concept came from NoSQL, where unenforced references were always the norm. Bringing it back into a relational tool gives SQL developers the same ergonomics — clickable, browsable, navigable data — on schemas they don't fully own.