Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Creating a foreign key fails: no unique constraint matches the referenced columns

ErrorERROR: 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.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 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';
  2. 2Match what you see, then jump to its fix

    1. 1The 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. 2The 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. 3indexdef in the first-step output ends with a WHERE clause.The only unique index on the column is a partial oneFix

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

Did this fix it?

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

Did this fix it?

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

Did this fix it?

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

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.