invalid_foreign_key there is no unique constraint matching given keys for referenced table “…” — 42830

Symptoms

A FOREIGN KEY references columns in the parent table that are not covered by a UNIQUE or PRIMARY KEY constraint. A foreign key must point at a uniquely-identified set of columns, so PostgreSQL raises SQLSTATE 42830 (invalid_foreign_key).

• Appears at CREATE TABLE/ALTER TABLE … ADD CONSTRAINT time.
• The referenced column(s) lack a unique or primary key constraint.
• A plain (non-unique) index on the parent is not enough.

What the server log shows

ERROR:  there is no unique constraint matching given keys for referenced table "customers"

Why PostgreSQL raises this — what the manual says

Section 5.5.5 Foreign Keys:

“A foreign key must reference columns that either are a primary key or form a unique constraint, or are columns from a non-partial unique index.”

A foreign key guarantees each child row points to exactly one parent row. That requires the referenced columns to be provably unique, which PostgreSQL enforces by demanding a matching UNIQUE or PRIMARY KEY constraint (not merely a unique index in all cases, and never a non-unique index).

Common causes

• Referencing a column that is indexed but not declared UNIQUE/PRIMARY KEY.
• Referencing a subset of a composite key that is not itself unique.
• Pointing at a column in a partitioned parent without a matching unique constraint that includes the partition key.

How to fix it

1. Add a unique constraint on the parent columns: ALTER TABLE customers ADD CONSTRAINT customers_pkey PRIMARY KEY (id); or ADD UNIQUE (…).
2. Reference the actual primary/unique key columns of the parent.
3. For composite keys, reference the full set of columns that form the unique constraint.

Diagnostic query

-- Show unique/primary key constraints on the parent table
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype IN ('p','u');

Related & next steps

• SQLSTATE 42830 — Invalid Foreign Key (full reference)
• Message: insert or update violates foreign key constraint
• Glossary: Foreign key

Reference: PostgreSQL 18 Section 5.5 “Constraints”.