🚨 PostgreSQL Error Guide: 'there is no unique constraint matching given keys' — Fix Foreign Keys

Overview

PostgreSQL raises this error when you try to create a foreign key whose referenced columns are not backed by a unique constraint or primary key on the parent table. It fires at DDL time, while the constraint is being added:

ERROR:  there is no unique constraint matching given keys for referenced table "customers"

You will see it from an ALTER TABLE ... ADD CONSTRAINT ... FOREIGN KEY, or inline in a CREATE TABLE:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_email) REFERENCES customers (email);
ERROR:  there is no unique constraint matching given keys for referenced table "customers"

A foreign key must point at columns Postgres can guarantee are unique — otherwise a child row could match many parents. If the referenced column set has no PRIMARY KEY or UNIQUE constraint covering exactly those columns, the FK cannot be created.

Symptoms

• Adding a foreign key fails immediately, before any data is checked.
• The parent column looks unique in the data, but no constraint enforces it.
• A composite foreign key fails even though each individual column is indexed.
• The FK works against one table but fails against a partitioned parent.
• A plain CREATE INDEX ... UNIQUE (as a non-constraint unique index with certain options) does not satisfy the requirement in edge cases.

Common Root Causes

• No unique constraint on the parent columns at all. The referenced column (e.g. email) is not the primary key and has no UNIQUE constraint — the most common cause.
• Column-set mismatch. A composite FK references (a, b) but the parent’s unique constraint covers (a, b, c) or (b, a). The referenced columns must match a unique constraint’s columns exactly (order matters for the constraint definition).
• Referencing a non-key subset. Pointing at a single column that is only part of a composite primary key, so that column alone is not unique.
• Partitioned parent table. Before the referenced-partitioned-table support in newer versions, and still with restrictions, a unique constraint on a partitioned table must include the partition key — and an FK referencing it must line up with that.
• A unique index that is not a constraint. A partial unique index (WHERE ...) or an expression unique index does not count as a unique constraint for FK purposes.
• Wrong parent table or schema. The FK references a table that has the unique constraint under a different name/schema than intended.

Diagnostic Workflow

First, inspect the parent table to see exactly which constraints exist:

\d customers

List the parent’s primary key and unique constraints and the columns each covers:

SELECT conname,
       contype,                        -- 'p' = primary key, 'u' = unique
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass
  AND contype IN ('p', 'u');

Confirm the exact column list the FK is trying to reference, and compare it to those constraints:

-- Columns of any unique/PK constraint on customers, in order:
SELECT c.conname, a.attname, array_position(c.conkey, a.attnum) AS ord
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
WHERE c.conrelid = 'customers'::regclass
  AND c.contype IN ('p', 'u')
ORDER BY c.conname, ord;

Check whether an apparent “unique index” is actually a constraint or just a partial/expression index (which will not satisfy the FK):

SELECT indexrelid::regclass AS index_name,
       indisunique, indpred IS NOT NULL AS is_partial,
       indexprs IS NOT NULL AS is_expression
FROM pg_index
WHERE indrelid = 'customers'::regclass;

Before checking for duplicates prior to adding a unique constraint, confirm the data really is unique:

SELECT email, count(*)
FROM customers
GROUP BY email
HAVING count(*) > 1;   -- any rows here block a UNIQUE constraint

Example Root Cause Analysis

A service added an orders.customer_email column and tried to reference customers.email:

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_email) REFERENCES customers (email);
-- ERROR: there is no unique constraint matching given keys for referenced table "customers"

The pg_constraint query showed customers had only a primary key on id. There was an ordinary index on email for lookups, but no UNIQUE constraint — so Postgres could not guarantee email identified a single parent row, and refused the FK.

The duplicate-check query returned no rows, confirming the data was already unique. The fix was to add the unique constraint on the parent first, then create the foreign key. On a large table this is done without a long lock by building the unique index concurrently and then attaching it as a constraint:

-- Build the supporting unique index without blocking writes:
CREATE UNIQUE INDEX CONCURRENTLY customers_email_uq ON customers (email);

-- Promote it to a real unique constraint (fast, uses the existing index):
ALTER TABLE customers
  ADD CONSTRAINT customers_email_uq UNIQUE USING INDEX customers_email_uq;

-- Now the foreign key can be created:
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_email) REFERENCES customers (email);

A verifying \d orders showed the FK in place and the endpoint that inserted orders stopped erroring on referential integrity.

Prevention Best Practices

• Decide the parent’s identifying key up front: give every table a primary key, and add a UNIQUE constraint to any column you intend other tables to reference.
• Make composite foreign keys reference the exact column set of a single unique/primary-key constraint on the parent — same columns, matching the constraint definition.
• On large parent tables, add the unique constraint via CREATE UNIQUE INDEX CONCURRENTLY then ADD CONSTRAINT ... USING INDEX to avoid a long ACCESS EXCLUSIVE lock and a full-table rebuild.
• Remember that partial and expression unique indexes do not satisfy FK requirements; you need a plain unique constraint over the referenced columns.
• For partitioned parents, ensure the unique constraint includes the partition key and that your FK design accounts for that restriction.
• Clean up duplicate parent rows (the HAVING count(*) > 1 check) before attempting to add the unique constraint, or the constraint creation itself will fail.

Quick Command Reference

\d parent_table                                   -- see PK/unique constraints and FKs
SELECT conname, contype, pg_get_constraintdef(oid)
  FROM pg_constraint WHERE conrelid = 'parent'::regclass;   -- list constraints
SELECT col, count(*) FROM parent GROUP BY col HAVING count(*) > 1;  -- duplicate check
CREATE UNIQUE INDEX CONCURRENTLY parent_col_uq ON parent (col);     -- online build
ALTER TABLE parent ADD CONSTRAINT parent_col_uq UNIQUE USING INDEX parent_col_uq;
ALTER TABLE child ADD CONSTRAINT child_fk
  FOREIGN KEY (col) REFERENCES parent (col);      -- now succeeds

Conclusion

This error is Postgres enforcing a core rule of referential integrity: a foreign key may only point at columns whose uniqueness is guaranteed by a PRIMARY KEY or UNIQUE constraint that matches the referenced columns exactly. The fix is almost always to add the missing unique constraint on the parent — mindful that partial/expression indexes and partition-key restrictions do not count — and to do it online with CREATE UNIQUE INDEX CONCURRENTLY on large tables. Design parent keys deliberately, match composite column sets precisely, and the foreign key will create cleanly.