Published: 2026-05-20

Polymorphic Foreign Keys in SQL: Source-Filter Scoping for Rails / Django / Laravel

Every ORM eventually invents the same pattern — a single column that points at different tables depending on a discriminator. Rails calls it belongs_to :commentable, polymorphic: true. Django calls it GenericForeignKey. Laravel calls it morphTo. SQL itself has no name for it because the relational model can't put a FOREIGN KEY constraint on it. The data is real; the schema can't describe it; your SQL client treats the column as just an integer. Source-filtered loose foreign keys close that gap.

TL;DR — In Jam SQL Studio you can declare two (or more) loose foreign keys on the same source column, each scoped by a source filter that decides when the FK activates. For a Rails-style polymorphic comments table, declare commentable_id → posts.id with source filter commentable_type = 'Post' and commentable_id → photos.id with source filter commentable_type = 'Photo'. The FK popover shows the right target per row, or a multi-tab view when more than one matches. Target filters additionally narrow the rows shown in the picker (e.g. deleted_at IS NULL for soft-delete-aware lookups). See Loose Foreign Keys — Source Filters for the full reference.

The polymorphic pattern, in one paragraph

You're building a comments feature. Users can comment on a Post or a Photo or a Video. You have three options:

1. One table per commentable type. post_comments, photo_comments, video_comments. Clean FKs, but every cross-cutting query needs UNION, and every new commentable type ships a new table.
2. One comments table with N nullable FK columns. post_id INT NULL REFERENCES posts, photo_id INT NULL REFERENCES photos, video_id INT NULL REFERENCES videos — exactly one is set per row, the others are NULL. Constraints are real, but the column count grows with every new commentable type, and the CHECK ((post_id IS NOT NULL)::int + (photo_id IS NOT NULL)::int + (video_id IS NOT NULL)::int = 1) is awkward.
3. One comments table with commentable_id + commentable_type. One row per comment regardless of what it's attached to. New commentable types cost nothing. But there is no FOREIGN KEY constraint you can put on commentable_id — the target table depends on the value of commentable_type, and SQL can't condition references on data.

Rails, Django, Laravel, ActiveRecord, SQLAlchemy, Ecto — every mature ORM offers option 3, because options 1 and 2 don't scale to the kind of small extensible domain models real apps end up needing. The cost is that commentable_id ends up looking like just another integer to anything outside the ORM. Open a SQL client and you can't click it, can't navigate, can't see what the comment is attached to without manually running queries.

Source filters — the FK that only sometimes points at this table

A source filter on a loose foreign key is a set of conditions evaluated against the source row that decides whether the FK activates. When the filter doesn't match, the cell is a plain value with no link.

For the polymorphic comments case, you declare two loose foreign keys on commentable_id:

Add a third for Video whenever Video ships, etc. The declarations are independent — each has its own id (UUID), its own target, its own source and target filters. Jam SQL Studio's filter chip + target picker support declaring all of this without leaving the grid:

What the popover does when more than one matches

The interesting case is rows where multiple declarations could match. The classic shape: a polymorphic column whose discriminator says exactly one thing — but you forgot to add the discriminator to one of the declarations. (Or you genuinely want a column to fan out to several tables, but that's rarer.)

Jam SQL Studio's FK popover handles this gracefully: when more than one loose FK matches a row, it shows them as tabs, one per target table, so you can navigate to any of the related records:

An overlap-detector runs at save-time: if you're about to save a declaration whose source filter could match the same rows as another declaration on the same column, Jam SQL Studio warns you before persisting. The most common case it catches is the "forgot the discriminator" case — two FKs without source filters both fire on every row. You can dismiss the warning to proceed or cancel and refine the filter. The check is intentionally conservative: it only warns when overlap is possible, not when filters are provably disjoint (e.g. type = 'Post' vs type = 'Photo').

Target filters — soft-delete-aware and status-scoped references

A target filter doesn't decide whether the FK fires; it narrows the rows fetched when browsing the target. The most common use is soft-delete awareness:

The target filter applies to the FK popover preview, the lookup picker when adding a filter, and the row picker when editing. It does not affect whether the source column links to a given target row — the data still links to whatever the column value says — it only controls what the picker shows. A small ▣ Filtered chip appears in the popover footer whenever a target filter is active, so you always know the preview is narrowed.

This is the same idea as Rails's default scopes or Django's Manager overrides, applied at the navigation layer of a SQL client rather than at the ORM layer.

A live peek pane while you write the filter

Source and target filters are non-trivial to get right. "Did commentable_type match the case I think it did? Are there Posts whose deleted_at is set?" The loose-FK editor includes a peek pane that runs the current filters as a SELECT * LIMIT 5 against the underlying table while you edit, with a live count of matching rows. You see the filter take effect immediately — no need to leave the dialog and run a separate SELECT in the editor.

Cross-engine support

Source filters are evaluated by the client against the row already fetched into the grid, so they don't add SQL to the source-side query. Target filters become WHERE clauses on the target-table query, and FK preview / picker / popover queries are engine-correct on all five engines. The same UI works the same way on SQL Server, PostgreSQL, MySQL (and MariaDB), Oracle, and SQLite — there's no engine matrix for this feature; it's complete on all five.

JSON columns get this too

A close cousin of polymorphic associations: a JSON document with a type discriminator and an id at $.target_id. The same machinery applies — declare loose FKs on JSON paths, gate them with source filters on the discriminator column or on another path in the same JSON. See Filter JSON Columns Across 5 SQL Engines for the JSON side of the story.

When this isn't the right tool

Loose foreign keys are navigation aids, not constraints. If you own the schema and the target column has a unique or primary key, prefer a real FOREIGN KEY. Source-filtered loose FKs are right when:

• You're working with an ORM-managed schema that uses polymorphic associations and you can't (or don't want to) restructure to one-table-per-type.
• The target depends on data, not just identifiers — i.e. it's structurally impossible to declare as a real FK.
• You want soft-delete or status-aware lookups in the SQL client without baking that filter into the database via a partial unique index.
• You're a read-only consumer of someone else's database (no DDL rights) and just want to navigate cleanly.

If the schema is yours and a real FK is possible, ship the real FK. The two are complementary: real FKs for what the schema knows, loose FKs for what the schema can't say.

Jam SQL Studio is free for personal use and runs natively on macOS (Apple Silicon and Intel), Windows, and Linux.

Download Jam SQL Studio

Further reading

• Loose Foreign Keys documentation — the full reference, including the editor UI, MetaInfo storage, and import/export.
• Source filters — polymorphic associations · Target filters — soft-delete and status scoping
• Loose Foreign Keys Without Constraints: When and Why — the original concept post, with the four use cases that drove the feature.
• Filter JSON Columns Across 5 SQL Engines — the JSON side of the same metadata layer; loose FKs work on JSONPaths too.