Polymorphic Associations in PostgreSQL: One commentable_id Column or a Foreign Key per Table?

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: A polymorphic association stores a type name and an id in two columns, and PostgreSQL has no foreign key that can point at a different table on each row, so nothing stops orphans or typos. For a small fixed set of parents, use one nullable foreign key per parent with a CHECK that exactly one is set; for many parents, use a shared supertype table. Keep the polymorphic pair only when the set of parents is open-ended, and document it in the ERD as virtual relations, which is how Schemity draws it.

In PostgreSQL, a polymorphic association is the one design where the database cannot help you: a foreign key names exactly one table, so a commentable_id that points at posts on one row and photos on the next is checked by nothing. If the set of parents is small and fixed, use one foreign key per parent with a CHECK that exactly one is set; if it is large, use a shared supertype table; keep the polymorphic pair only when the list of parents is genuinely open.

Most schemas get a polymorphic association without anyone deciding on one. Rails has belongs_to :commentable, polymorphic: true, Laravel has morphTo, and Django has GenericForeignKey, and each makes it one line of model code. The Rails guide to polymorphic associations shows the migration it produces: an id column and a type column, with no foreign key between them and any other table.

What does a polymorphic association look like in PostgreSQL?

Two columns on the child table, one holding a table or class name and one holding a primary key value from that table:

CREATE TABLE comments (
    id               bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    commentable_type text   NOT NULL,  -- 'Post' or 'Photo'
    commentable_id   bigint NOT NULL,
    content          text   NOT NULL
);

CREATE INDEX ON comments (commentable_type, commentable_id);

Django spells the same idea as content_type_id (a real foreign key to django_content_type) plus object_id, and Laravel uses the *_type / *_id pair exactly as Rails does. In every version, the column that actually points at a parent row, commentable_id or object_id, carries no constraint.

Why can't PostgreSQL put a foreign key on commentable_id?

Because REFERENCES takes one table. commentable_id REFERENCES posts (id) would reject every comment on a photo, and there is no syntax for "the table named in the other column". Everything a foreign key normally does for you is gone:

• Orphans. Deleting a post leaves its comments behind, because there is no ON DELETE CASCADE to fire. The cleanup lives in a model callback, which a bulk DELETE in a console or a second service never runs.
• Dangling ids. Nothing checks that post 4812 exists when a comment claims to belong to it.
• Bad type names. 'Post', 'post' and a class renamed in a refactor are all just text. After a rename, every old row points at a type the application no longer knows. A CHECK (commentable_type IN ('Post', 'Photo')) closes the typo half of this, since PostgreSQL then rejects 'post', but it still says nothing about whether the id exists.
• Invisible structure. Every tool that reads relationships from the catalog, from ERD software to BI join suggestions, sees comments as connected to nothing.

What are the alternatives to a polymorphic association?

There are three, and each one gives the database back a real foreign key.

One foreign key per parent (an exclusive arc). Each possible parent gets its own nullable column, and a CHECK enforces that exactly one is set. PostgreSQL has had num_nonnulls since 9.6, which makes the check one line:

CREATE TABLE comments (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id  bigint REFERENCES posts (id)  ON DELETE CASCADE,
    photo_id bigint REFERENCES photos (id) ON DELETE CASCADE,
    content  text NOT NULL,
    CHECK (num_nonnulls(post_id, photo_id) = 1)
);

A supertype table. Create commentables (id bigint PRIMARY KEY), give posts and photos a primary key that is also a foreign key to it, and point comments.commentable_id at commentables. One foreign key, enforced, however many kinds of parent you add.

A comment table per parent. post_comments and photo_comments, each with an ordinary foreign key. It is the simplest schema and the right one when comments on different parents carry different columns anyway.

Should I use polymorphic associations in Postgres?

Rarely, and on purpose when you do. A workable rule:

• Two to five fixed parents: use the exclusive arc. The nullable columns are cheap, the CHECK is one line, and every reference is enforced.
• Many parents, or you need to list "everything that can be commented on": use the supertype table.
• Children that differ per parent: use a table per parent.
• An open-ended set of parents, such as plugins, audit logs or activity feeds that attach to any table in the system: the polymorphic pair is a reasonable trade, as long as the application owns the integrity and the schema says so. Give the type column a CHECK listing the allowed values, so at least a misspelled type cannot reach the table.

That last condition is the one teams skip. A polymorphic column that nobody has documented is a relationship that exists only in model code, and the next engineer reading the database finds a bigint column that joins to nothing.

How do you draw a polymorphic association in an ERD?

When you design the next one, the diagram should show both kinds of reference honestly: the ones the database enforces and the ones it cannot. Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

A real relation in Schemity only draws what the database would accept, so there is no way to draw commentable_id as a foreign key to two tables. What you draw instead is a virtual relation, and it takes four steps:

1. Drag a relation from posts to comments, exactly as you would for a real foreign key.
2. In the relation dialog, switch to the Virtual relation tab.
3. Pick the existing commentable_id column on the comments side. Nothing new is created: a virtual relation points at a column you already have.
4. Type a description such as "Post comments" and click Save.

Repeat it from photos to the same commentable_id column with "Photo comments". The diagram now shows comments depending on both parents, with a dashed line for each, and neither line ever reaches a generated migration or a DBML export. Schemity draws each description along its line, so a reader knows what each dashed line means without opening a dialog. If the type column has a CHECK (commentable_type IN ('Post', 'Photo')), the column is underlined as an enum-like field and the entity footer counts the constraint, so the one guard the database does provide is visible too. Virtual relations survive a re-sync from the database, and the cardinality dialog hides ON DELETE and ON UPDATE for them, because nothing performs those actions.

If you choose the exclusive arc instead, it draws as what it is: post_id and photo_id as real foreign keys, each with the green N badge that marks a nullable column, and each parent end drawn as optional, because any single comment belongs to only one of them, and the num_nonnulls CHECK counted in the entity footer. Schema lint then flags either key if it has no index, which matters here because each parent's delete looks up its children through that column. Either way, the reader sees the real shape of the model, and the choice between enforced and documented is visible on the canvas rather than buried in a model file.

Related reading

The broader question of when a schema should rely on documented rather than declared references is covered in when skipping foreign key constraints is right. For what a cascade does once it is declared, see why ON DELETE CASCADE is invisible in most ERDs, and for the key type behind every _id column in this post, UUID vs bigint primary keys in PostgreSQL.