What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
A PostgreSQL foreign key references one table; it cannot use a type value to choose among several parent tables. For a small, stable set of parent types, use one nullable foreign key per type and a CHECK constraint requiring exactly one parent. For an intentionally open-ended set, a commentable_type/commentable_id pair can be more extensible, but the application must take responsibility for verifying references and handling deletions.
Why one ID cannot reference several tables
A conventional foreign key names a specific referenced table and columns. Its target must be backed by a primary key, unique constraint, or qualifying unique index, and the referencing and referenced columns must have compatible types and counts. A commentable_id value alone cannot tell PostgreSQL whether to look in posts, photos, or another table. [PostgreSQL 18: Constraints]
As an Amazon Associate I earn from qualifying purchases.
That is the key distinction: a type/ID pair can describe a polymorphic relationship in application data, but it is not a foreign key covering all the possible target tables. PostgreSQL cannot use an ordinary foreign-key constraint to reject an ID that does not exist in the table selected by the type value.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Choose based on how much integrity and flexibility you need
| Pattern | Can the database verify the parent exists? | Adding a parent type | Main trade-off |
|---|---|---|---|
| One type/ID pair | No, not with an ordinary foreign key when targets vary by type | Usually does not require a new child-table column, but application resolution must support the new type | Extensible representation; validation, deletion handling, and orphan cleanup belong to application logic or another explicitly designed mechanism |
| One nullable foreign key per type plus an exactly-one check | Yes; each foreign key checks its own target table | Requires a schema change and corresponding query/application updates | Strong direct references for a fixed set, with more columns as supported types grow |
| Shared parent registry | A child can reference the common registry row | Can support more kinds behind one common identity | Adds a registry row and lifecycle relationship; subtype and registry alignment must be maintained |
| Separate child or association table per parent type | Yes, through each table’s direct foreign key | Requires another association or child structure | Explicit references, with shared fields or cross-type reads potentially needing duplication or a union/view |
The foreign-key behaviors in this comparison follow PostgreSQL’s constraint model. The registry and separate-table patterns are schema-design options built around those ordinary foreign keys, not automatic polymorphic features. [PostgreSQL 18: Constraints]
#1 Best Overall
For a small fixed set, use an exclusive arc
An exclusive arc stores one nullable FK column for each supported parent type and adds a row-local check that exactly one is populated. Each REFERENCES clause verifies that its named parent exists; the check prevents a child from naming no parent or multiple parents.
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,
body text NOT NULL,
CONSTRAINT comments_exactly_one_parent
CHECK (num_nonnulls(post_id, photo_id) = 1)
);The ON DELETE CASCADE clauses above are illustrative, not a universal policy. Choose the action that matches the child record’s meaning:
Rank #2
CASCADE: delete the child rows when their parent is deleted.RESTRICTorNO ACTION: prevent a parent deletion that would leave referencing rows, subject to the timing and constraints involved.NO ACTIONis the default.SET NULL: clear the referencing column when the parent is deleted. With an exactly-one check, that can make the child row invalid, so the check and child lifecycle policy must be designed together.
PostgreSQL documents these FK actions and notes that they remain subject to other constraints on the table. A check constraint evaluates the row being checked; it is not a way to look up a parent in another table. [PostgreSQL 18: Constraints] [PostgreSQL 16: Constraints]
Free tools Windows power users keep installed
One-click scans. No signup required.
Index the referencing columns when the workload calls for it
PostgreSQL requires an appropriate unique target on the referenced side, but it does not automatically create an index on the referencing FK columns. Index columns such as post_id or photo_id when lookups or parent updates and deletes make them useful; the right indexes depend on access patterns. [PostgreSQL 18: Constraints]
Be deliberate with nulls and composite keys
A check constraint passes when its expression evaluates to true or null, so express the exactly-one rule explicitly or use appropriate NOT NULL constraints where they fit. For a composite foreign key, PostgreSQL’s default MATCH SIMPLE allows the constraint to be bypassed if any referencing component is null. MATCH FULL instead requires all components to be null or all to match. [PostgreSQL 16: Constraints] [PostgreSQL 18: Constraints]
Use a type/ID pair only with an explicit integrity owner
A pair such as commentable_type and commentable_id stores a discriminator and an ID. The application reads the discriminator, selects the corresponding table, and resolves the ID. This is a reasonable trade-off when parent types are genuinely open-ended or change frequently and schema changes for each new type are undesirable.
The cost is that PostgreSQL’s ordinary FK constraint cannot verify that the chosen parent exists. A deletion can leave orphaned child rows unless the application, a trigger strategy, or another explicit process handles it. Likewise, a CHECK constraint cannot make this a cross-table existence check: PostgreSQL does not support checks that reliably depend on other rows or tables. Assign a clear owner for validation, concurrent changes, deletion behavior, orphan detection, and cleanup. [PostgreSQL 16: Constraints]
Consider a registry or separate association tables when the model fits
Shared parent registry
A registry table such as commentables can provide a common identity. Comments reference that table through one FK, while subtype records share the registry key. This preserves a database-enforced reference to a registry row, but it adds a row and lifecycle relationship. The design still needs to ensure that each registry entry corresponds to the intended subtype and that creation and deletion keep them aligned.
One association table per parent type
Tables such as post_comments and photo_comments can each declare a direct FK to their respective parent. This keeps references explicit. If the child fields are shared, separate structures can duplicate them; if the application needs a unified view across types, it may need a union or view layer.
Do not rely on inheritance to supply missing foreign keys
PostgreSQL inheritance can make queries against a parent table include descendant rows by default, but primary-key, unique, and foreign-key constraints are not inherited by child tables. Inheritance therefore does not automatically turn separate descendant tables into one FK target for a polymorphic association. [PostgreSQL 17: Inheritance]
Practical decision
- Choose per-type nullable FKs plus an exactly-one check when the parent types are known and stable and PostgreSQL should enforce parent existence and deletion rules.
- Choose a type/ID pair when the parent set is intentionally open-ended and the team accepts responsibility for application-level validation and cleanup.
- Choose a shared registry when many parent kinds need one stable identity and the extra registry/subtype lifecycle is acceptable.
- Choose separate association tables when explicit direct FKs matter more than a single shared child table.
These patterns differ in constraints and schema maintenance; the documented behavior does not establish a universal performance winner. Benchmark a representative workload if query speed is part of the decision.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
