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
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
WHEREclause), which can't back a foreign key.
How to fix it
-
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);
-
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
23503foreign key constraint violation42P10invalid column reference1822MySQL: Missing index for foreign key constraint1215MySQL: Cannot add foreign key constraint
More syntax error or access rule violation errors
42601syntax error at or near42501permission denied42846cannot coerce42803column must appear in the GROUP BY clause42804datatype mismatch42P18could not determine data type of parameter42703column does not exist42883function does not exist
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.
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.