datatype_mismatch foreign key constraint “…” cannot be implemented — 42804

Symptoms

A foreign key could not be created because the referencing and referenced columns have incompatible types. PostgreSQL raises SQLSTATE 42804 (datatype_mismatch).

• The FK column type doesn’t match the referenced key type.
• A DETAIL names both column types.
• Common when one side is integer and the other bigint/text.

What the server log shows

ERROR:  foreign key constraint "orders_customer_id_fkey" cannot be implemented
DETAIL:  Key columns "customer_id" and "id" are of incompatible types: integer and bigint.

Why PostgreSQL raises this — what the manual says

Section 5.5.5 Foreign Keys:

“Of course, the number and type of the constrained columns need to match the number and type of the referenced columns.”

A foreign key compares referencing values against the referenced key using an equality operator. When the column types are not comparable, no such operator applies and PostgreSQL cannot implement the constraint, reporting 42804.

Common causes

• Mismatched integer widths (integer vs bigint).
• Different types entirely (e.g. text vs uuid).
• An FK referencing a column with a different domain/type.

How to fix it

1. Align the column types on both sides of the relationship.
2. Change the referencing column’s type to match the referenced key.
3. Ensure the referenced column is a primary/unique key of a matching type.

Related & next steps

• SQLSTATE 42804 — Datatype Mismatch (full reference)
• Message: no unique constraint matching given keys
• Glossary: Foreign key

Reference: PostgreSQL 18 Section 5.4 “Constraints”.