Polymorphic associations are common when a single table can reference multiple other tables. A typical implementation is one table with multiple nullable foreign keys:

CREATE TABLE my_poly_assocs (
  id bigserial PRIMARY KEY,
  assoc_a_id bigint REFERENCES assoc_a(id),
  assoc_b_id bigint REFERENCES assoc_b(id),
  assoc_c_id bigint REFERENCES assoc_c(id)
);

The intent is simple: each row should reference exactly one of these associations. But the database won’t enforce that automatically. Without extra constraints, you can end up with:

• No association set (all NULL)
• Multiple associations set (invalid state)

PostgreSQL has a clean solution for this.

The num_nonnulls function

PostgreSQL provides a built-in function called num_nonnulls. It returns the number of arguments that are not NULL.

That makes it perfect for enforcing “exactly one” semantics:

ALTER TABLE my_poly_assocs
ADD CONSTRAINT exactly_one_assoc_referenced
CHECK (num_nonnulls(assoc_a_id, assoc_b_id, assoc_c_id) = 1);

This constraint guarantees:

• At least one foreign key is set
• No more than one foreign key is set

If a row violates the rule, the insert or update fails immediately.

Why this is better than application-level checks

You could enforce this rule in your application layer, but that leaves room for:

• Race conditions
• Multiple services writing to the same database
• Future code paths forgetting the rule

A CHECK constraint keeps the invariant inside the database, where it belongs.

Variations

If your requirement is “at most one” instead of “exactly one”, you can adjust the constraint:

CHECK (num_nonnulls(assoc_a_id, assoc_b_id, assoc_c_id) <= 1)

If you later add a new polymorphic target, you must update the constraint to include the new column.

When to use this pattern

This approach works well when:

• You need strict relational integrity.
• The set of polymorphic targets is finite and known.
• You want predictable query performance without an additional type column.

If your targets are dynamic or numerous, a more classic polymorphic design (e.g. target_type + target_id) may be more flexible, though it trades off referential integrity.

Conclusion

num_nonnulls is a small but powerful feature in PostgreSQL. It allows you to enforce a subtle but important invariant with a single CHECK constraint, keeping your polymorphic associations consistent and safe at the database level.

It’s one of those features that feels obvious once you know it exists.