iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A PostgreSQL foreign key references one specific table; a single commentable_id cannot use commentable_type to switch its target among several tables. For a small, stable set of parent types, use one nullable foreign-key column per type plus a CHECK constraint that requires exactly one parent. For a genuinely open-ended set, a type-and-ID pair can be a reasonable trade-off if your application takes responsibility for validation and cleanup.
What PostgreSQL can enforce
A foreign key checks that the referenced value exists in its designated target table, whose referenced columns must be a primary key, unique constraint, or qualifying unique index. The number and types of referencing and referenced columns must match. A regular foreign key cannot choose a target table from another column’s value. PostgreSQL 18 documents these foreign-key requirements.
This distinction matters for a comment that may belong to either a post or a photo. A commentable_type value can tell application code which table to query, but it does not make commentable_id a database-enforced reference to both tables. A CHECK constraint cannot fill that gap: PostgreSQL checks are for conditions on the row being checked, not for verifying data in another table. PostgreSQL’s constraint documentation explains this limitation.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Choose a design based on parent-set stability
| Design | Database checks parent exists? | Adding a parent type | Deletion handling | Best fit |
|---|---|---|---|---|
| Type and ID pair | No ordinary foreign key can validate the selected table. | Usually no new child column, but application resolution logic must support the type. | Application, trigger, or another explicit process must handle dependent rows and orphans. | Parent types are intentionally open-ended and the team accepts application-owned integrity. |
| Nullable foreign key per parent type, plus an exactly-one check | Yes; each foreign key checks its own target table. | Add a column, foreign key, check logic, and any affected query handling. | Declared foreign-key actions can govern each relationship, subject to other constraints. | A small, known set of parent types and a priority on database-enforced references. |
| Shared parent registry | Comments can reference the common registry key; subtype alignment needs its own design. | Can add kinds under a common identity, while maintaining registry and subtype lifecycle. | The registry relationship can use foreign-key actions; subtype cleanup still needs a policy. | A shared identity across kinds is valuable enough to justify another schema layer. |
| Separate association or child table per type | Yes, through a direct foreign key in each table. | Add another table and corresponding read path. | Each direct foreign key can declare its own action. | A few types, explicit relationships, and acceptable duplication or union-based reads. |
Use one foreign key per type for a small, fixed set
For mutually exclusive parent choices, put a nullable reference to each supported parent table on the child row. A row-local check ensures exactly one reference is populated; each REFERENCES clause independently verifies that its target exists.
#1 Best Overall
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 example’s ON DELETE CASCADE is illustrative, not a universal choice. PostgreSQL supports actions including the default NO ACTION, RESTRICT, CASCADE, and SET NULL. Choose according to whether comments should be deleted, preserved, or prevent parent deletion. Foreign-key actions remain subject to the child table’s other constraints. In particular, SET NULL on the populated column conflicts with this exactly-one check unless the child lifecycle and constraint are designed to allow that result. See PostgreSQL’s foreign-key action and null-handling rules.
This pattern makes the integrity rule visible in the schema, but adding a supported parent type requires a schema change and updates to relevant queries. It also does not automatically index the referencing columns. Consider indexes on columns such as post_id and photo_id for common child lookups and parent update or delete checks; choose them for the workload rather than assuming the foreign key created them.
Rank #2
Use a type-and-ID pair only when extensibility justifies the trade-off
A pair such as commentable_type and commentable_id keeps the child table compact and lets application code route each row to its parent table. Its essential limitation is that an ordinary foreign key cannot validate that the ID exists in whichever table the type names. If a parent is deleted, the database will not automatically find and remove rows in this polymorphic relationship.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Before adopting this design, assign clear ownership for:
- Validating the type and checking that the corresponding parent exists when a child is created or reassigned.
- Handling parent deletion so child rows are deleted, retained, or otherwise resolved according to policy.
- Detecting and cleaning up orphaned children, including those left by failed or concurrent application operations.
- Keeping type values and table-resolution code consistent as the set of parent types changes.
A row-level CHECK can restrict allowed discriminator values, but it cannot confirm that commentable_id exists in a table selected by that value. A trigger or application mechanism may implement an explicitly designed policy; neither should be mistaken for the built-in semantics of a foreign key.
Consider a shared registry when parent identities should be unified
A registry table such as commentables can assign a common identity to objects of several kinds. Comments then reference that one table with an ordinary foreign key, preserving a database check that the shared identity exists. Each concrete parent still needs a lifecycle relationship to its registry row, and the design must ensure the subtype record and registry stay aligned. The registry adds a schema layer; it does not automatically enforce every rule about subtype data.
Other implementation details that affect correctness
Null behavior in checks and composite foreign keys
A PostgreSQL CHECK passes when its expression evaluates to true or null. For an exactly-one-parent rule, an explicit null-count expression such as num_nonnulls(post_id, photo_id) = 1 avoids relying on a check that might evaluate to null. Add NOT NULL where a value is independently mandatory. For composite foreign keys, the default MATCH SIMPLE allows the reference to go unmatched if any referencing component is null; MATCH FULL instead requires either all components to be null or all to match. PostgreSQL 18 describes both behaviors.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Indexes on the referencing side
The referenced key needs to be unique, but PostgreSQL does not automatically create an index on the referencing columns when you declare a foreign key. Index those columns when lookups or parent updates and deletions make it useful; the right choice depends on access patterns and data volume, not on the polymorphic label alone. The PostgreSQL documentation covers foreign-key indexing guidance.
Inheritance does not supply missing foreign-key enforcement
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 turn a type-and-ID pair into a foreign key across descendant tables. PostgreSQL’s inheritance documentation states which constraints are not inherited.
Practical decision
- Choose per-type foreign keys with an exactly-one check when the parent list is small and stable and you want PostgreSQL to reject nonexistent parents.
- Choose a type-and-ID pair when parent types are deliberately open-ended and your team is willing to own validation, delete behavior, and orphan cleanup.
- Choose a shared registry when a common identity is useful and you can manage the additional subtype lifecycle relationship.
- Choose separate association tables when direct references matter and duplicating structures or combining reads is manageable.
These are integrity and maintenance trade-offs, not an established performance ranking. The PostgreSQL sources describe constraint behavior, not comparative benchmarks; performance claims require testing against the application’s own queries and workload.
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.
