Incident brief
No unique constraint for foreign key
A foreign key must reference columns backed by a PRIMARY KEY or UNIQUE constraint on the parent; referencing unconstrained columns raises this invalid-foreign-key error.
What lands in your log
ERROR: there is no unique constraint matching given keys for referenced table "fk_parent"
In 10 seconds
- What triggers it
- Define a foreign key that references parent columns with no PRIMARY KEY or UNIQUE constraint.
- Fix
- Add a PRIMARY KEY or UNIQUE constraint on the referenced parent columns first.
- Proof
- Reproduced on PostgreSQL 18.4 → A foreign-key definition reproduces SQLSTATE 42830 because the parent columns are not unique.
Fix
What to do right now
Application-level steps for this error.
- Add a PRIMARY KEY or UNIQUE constraint on the referenced parent columns first.
- Then create the foreign key so it points at the now-unique target.
ALTER TABLE fk_parent ADD CONSTRAINT fk_parent_a_key UNIQUE (a);
CREATE TABLE fk_child (parent_ref int REFERENCES fk_parent(a));For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
The referenced parent columns need a PRIMARY KEY, UNIQUE constraint, or suitable non-partial unique index in the same column order.
Unique parent keys available for the FK
Replace the parent table; compare index keys with the REFERENCES column list.
SELECT i.indexrelid::regclass AS index_name,
i.indisprimary, i.indisunique, i.indpred IS NOT NULL AS is_partial,
pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
WHERE i.indrelid = to_regclass('<schema.parent_table>')
AND i.indisunique
ORDER BY i.indisprimary DESC, index_name::text;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 18 Documentation, Appendix A. PostgreSQL Error Codes (Table A.1, Class 42, Syntax Error or Access Rule Violation)
42830 → invalid_foreign_keyRead the full section on postgresql.org →
Referencing non-unique parent columns
The referenced table has no unique constraint on the target columns, so PostgreSQL raises 'there is no unique constraint matching given keys for referenced table fk_parent'.The session continues normally
ADD CONSTRAINT failed outside an explicit transaction, so there's no partial change for the session to roll back.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Define a foreign key that references parent columns with no PRIMARY KEY or UNIQUE constraint.
- 2PostgreSQL looks for a unique constraint on the referenced columns and finds none.
- 3The FK definition is rejected with SQLSTATE 42830.
A child table's foreign key pointed at a parent column that was never made unique, and the constraint could not be created.
CREATE TABLE fk_parent(a int);CREATE TABLE fk_child (parent_ref int REFERENCES fk_parent(a));SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
CREATE TABLEERROR: there is no unique constraint matching given keys for referenced table "fk_parent" session_after_error
---------------------
ok
(1 row)The foreign key is accepted once the parent columns are unique.
Without this
Before: the FK has no unique target
With this, tested
After: a primary key backs the reference
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
The runbook for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-24 (isolated lab, PostgreSQL 18.4)
- Verification scope
- Verified against PostgreSQL 18.4 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.