// PostgreSQL error reference

PostgreSQL 42830: invalid foreign key

A foreign key points at columns that aren't covered by a primary key or unique constraint on the referenced table.

SQLSTATE
42830
Name
invalid_foreign_key
Category
Syntax Error or Access Rule Violation
Error message
there is no unique constraint matching given keys for referenced table "customers"

What it means

A foreign key must reference columns that are guaranteed unique in the parent table, through its primary key or a UNIQUE constraint (or a unique index without a predicate) on exactly those columns. Otherwise PostgreSQL can't tell which parent row a child refers to, and creating the foreign key fails with 42830.

The columns must match as a set: a unique constraint on (tenant_id, id) doesn't let a foreign key reference id alone.

Common causes

  • The referenced column has no primary key or unique constraint at all.
  • The foreign key references a subset or superset of the columns of the unique constraint.
  • The unique index on the parent is partial (has a WHERE clause), which can't back a foreign key.

How to fix it

  1. Add a primary key or unique constraint on the referenced columns, then create the foreign key.

    ALTER TABLE customers ADD CONSTRAINT customers_email_key UNIQUE (email);
    ALTER TABLE orders ADD FOREIGN KEY (customer_email) REFERENCES customers (email);
  2. Or reference the existing key, using the same column list.

    ALTER TABLE orders
        ADD FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id);

Reproduce it safely

CREATE TEMP TABLE p (id int);
CREATE TEMP TABLE c (p_id int REFERENCES p (id));  -- ERROR: there is no unique constraint matching given keys for referenced table "p"

Related errors

More syntax error or access rule violation errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.