Creating a foreign key fails: no unique constraint matches the referenced columns
ERROR: there is no unique constraint matching given keys for referenced table "bar"Short answer
A foreign key may only point at columns that are a primary key, a unique constraint, or a non-partial unique index. The error means the columns you named after REFERENCES are none of these. Add a unique constraint on exactly those columns, or point the key at the columns that already have one.
Fix it fast
1Find out which cause you have
List the constraints and indexes of the referenced table, then compare them with the exact column list after REFERENCES. The list must match a unique or primary key as a whole.
SELECT conname, contype, pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'bar'::regclass; SELECT indexdef FROM pg_indexes WHERE tablename = 'bar';2Match what you see, then jump to its fix
- 1
The first-step query shows no constraint of type p or u on that column.The referenced column has no primary key or unique constraintFix - 2
The foreign key lists two or more columns, and the constraints on the parent are on single columns.Each column is unique alone, but the foreign key uses both togetherFix - 3
indexdef in the first-step output ends with a WHERE clause.The only unique index on the column is a partial oneFix
- 1
What does the error look like?
ERROR: there is no unique constraint matching given keys for referenced table "bar"ALTER TABLE ... ADD FOREIGN KEY fails while CREATE TABLE of the parent worked
Cause 1 of 3
The referenced column has no primary key or unique constraint
1Check
An ordinary column, even one that happens to hold distinct values, does not qualify. Postgres needs a declared primary key or unique constraint, or a unique index.
SELECT count(*) AS rows, count(DISTINCT code) AS distinct_codes FROM bar;2Fix
Add the constraint to the referenced table first (it fails if duplicates exist, so clean them up), then create the foreign key.
ALTER TABLE bar ADD CONSTRAINT bar_code_key UNIQUE (code);
ALTER TABLE foo ADD FOREIGN KEY (bar_code) REFERENCES bar (code);3Verify
The constraint is listed on bar.
SELECT conname, contype FROM pg_constraint WHERE conrelid = 'foo'::regclass;Confirmed by postgresql.org
Cause 2 of 3
Each column is unique alone, but the foreign key uses both together
1Check
The referenced list must itself be the unique constraint. Two single-column constraints do not make the pair unique.
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'bar'::regclass AND contype IN ('p','u');2Fix
Add a multicolumn unique constraint on exactly the referenced columns, or change the foreign key to reference the single column that is already unique.
ALTER TABLE bar ADD CONSTRAINT bar_a_b_key UNIQUE (a, b);
ALTER TABLE foo ADD FOREIGN KEY (a, b) REFERENCES bar (a, b);3Verify
Insert a child row and see that it is accepted only when the pair exists in bar.
SELECT conname FROM pg_constraint WHERE conrelid = 'foo'::regclass AND contype = 'f';Confirmed by postgresql.org
Cause 3 of 3
The only unique index on the column is a partial one
1Check
A partial unique index (CREATE UNIQUE INDEX ... WHERE ...) only guarantees uniqueness for some rows, so a foreign key cannot rely on it. The documentation names non-partial unique indexes as the accepted form.
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'bar' AND indexdef LIKE '% WHERE %';2Fix
Build a full unique index (or a normal unique constraint) on the same columns, then add the foreign key.
CREATE UNIQUE INDEX CONCURRENTLY bar_code_full_idx ON bar (code);
ALTER TABLE foo ADD FOREIGN KEY (bar_code) REFERENCES bar (code);3Verify
The foreign key is created without the error.
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conrelid = 'foo'::regclass AND contype = 'f';Confirmed by postgresql.org
How do I stop it coming back?
- Declare PRIMARY KEY or UNIQUE in the same migration that creates the parent table, before any table refers to it.
- Reference the parent's primary key where you can; REFERENCES bar without a column list uses it.
- When a migration adds a unique constraint to a table with existing data, run the duplicate check first.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Related fixes
Databases
INSERT ... ON CONFLICT fails because no unique constraint matches the conflict target
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
PostgreSQL must find a unique index that covers exactly the columns in your ON CONFLICT list.
4 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix