A developer creates a unique index on a lookup table's code column, then tries to add a foreign key from another table referencing that column, and the database refuses with 'no unique constraint matching given keys'. What does the referenced side actually have to provide for a foreign key to be legal?

seniorshould knowfreq 35%

answer

FK needs provable whole-table uniqueness
standard: PRIMARY KEY or UNIQUE constraint
partial / expression / invalid index → rejected
deferrable constraint can't back an FK
full key column list, same order

basics

~20 s

The referenced columns must be provably unique for every row of the table. The standard requires a declared PRIMARY KEY or UNIQUE constraint; engines that accept a bare unique index still require it to be full-table, non-partial, over plain columns and immediately checked. Partial and expression unique indexes never qualify.

solid answer

~60 s

A foreign key needs the parent side to guarantee at most one matching row for any key value, over the whole table. The SQL standard expresses that as: the referenced columns must be the columns of a primary key or a unique constraint on the referenced table.

Engines vary in how literally they take it:

Strict engines accept only a declared PRIMARY KEY or UNIQUE constraint; a bare unique index is not enough.
Others will accept any unique index that is valid, non-partial, over plain columns (no expressions), and immediately checked — a deferrable unique constraint or a partial/filtered index is rejected.
Some engines historically accepted even a non-unique index on the parent side, which is non-standard and allows a child row to "reference" several parents.

So the common failures are: referencing columns covered only by a partial unique index, by an expression index such as lower(code), by a non-unique index, or referencing a subset/superset of the key columns. The portable habit is to declare the parent key as a named constraint and reference exactly its column list, in order.

A foreign key needs the parent side to guarantee **at most one matching row for any key value, over the whole table**. The SQL standard expresses that as: the referenced columns must be the columns of a primary key or a unique constraint on the referenced table. Engines vary in how literally they take it: - Strict engines accept only a declared PRIMARY KEY or UNIQUE constraint; a bare unique index is not enough. - Others will accept any unique index that is **valid, non-partial, over plain columns (no expressions), and immediately checked** — a deferrable unique constraint or a partial/filtered index is rejected. - Some engines historically accepted even a non-unique index on the parent side, which is non-standard and allows a child row to "reference" several parents. So the common failures are: referencing columns covered only by a **partial** unique index, by an **expression** index such as `lower(code)`, by a non-unique index, or referencing a subset/superset of the key columns. The portable habit is to declare the parent key as a named constraint and reference exactly its column list, in order.

code

sql · 9 lines

sql

-- OK: declared key
ALTER TABLE lookup ADD CONSTRAINT lookup_code_key UNIQUE (code);

-- Not an FK target: uniqueness only over a subset of rows
CREATE UNIQUE INDEX lookup_code_active_idx
  ON lookup (code) WHERE retired_at IS NULL;

-- Not an FK target: keys on a derived value
CREATE UNIQUE INDEX lookup_code_ci_idx ON lookup (lower(code));

go deeper

for a junior

Say that the referenced columns must be a primary key or unique key of the parent table, and that a plain index is not a key.

for a middle

Add the exclusions — partial, expression, invalid and deferrable uniqueness do not qualify — and that the referenced column list must match the key exactly.

for a senior

Diagnose from the error: inspect what uniqueness exists, why it does not qualify, and propose the surrogate-key fix; mention the missing child-side index as the adjacent production issue.

for a principal

Use it as the argument for a schema convention: logical keys are declared as constraints because they are the only objects other constraints and tooling may cite; conditional uniqueness implies a surrogate key exists.

Why the parent side must be a key

Referential integrity says: every non-null child key value must match a parent row. For the engine to enforce that cheaply and unambiguously it needs two things:

Uniqueness — otherwise "the parent row" is not well defined. If two parents share the key, cascading updates and deletes have no single target, and the referential predicate degenerates into "at least one match", which the standard does not define.
An access path — the check runs on every child insert/update and, in the other direction, on every parent delete/update. Both need an index; a sequential scan per row would be unusable.

A unique constraint gives both at once, which is why the standard phrases the rule in terms of constraints rather than indexes.

What engines actually check

When you add a foreign key, the engine looks for something on the parent table that proves uniqueness over the referenced column list. Implementations fall into three camps:

Standard-strict. Only a PRIMARY KEY or UNIQUE constraint counts. A hand-built unique index is invisible to the check even though it enforces the same rule. This is the behaviour to assume if you want portable DDL.

Index-based. The engine scans the parent's indexes for one that is unique, currently valid, not partial, built on plain columns rather than expressions, and immediately checked rather than deferrable. A bare CREATE UNIQUE INDEX therefore works here, but the exclusions bite in practice:

A partial unique index (... WHERE deleted_at IS NULL) guarantees uniqueness only over the filtered subset, so the FK cannot rely on it — and the rows it excludes are exactly the ones a child might point at.
An expression index on lower(code) keys on a derived value, not on code; two rows can share lower(code) casing variants — no, worse: the index proves lower(code) unique, which says nothing about code being unique as a lookup key by itself, and there is no way to match child values against it.
A deferrable unique constraint may be temporarily violated inside a transaction, so it cannot back a check that must hold at each statement.
An index still being built, or built and marked invalid after a failed concurrent build, does not count.

Lax. A few engines only require an index whose leading columns are the referenced columns, unique or not. This is explicitly non-standard: it permits one child row to match several parent rows, and it makes cascade behaviour engine-specific. Do not rely on it.

Column list, order and subsets

The reference must name the full key column list. Given UNIQUE (tenant_id, code), you cannot create a foreign key on code alone — code is not unique by itself, so the reference is meaningless. Referencing a superset fails too, because there is no key over those columns.

Order matters for matching the key in engines that resolve by index: REFERENCES lookup (tenant_id, code) finds the key, while a different order may not, even though the constraint semantics would be equivalent.

The child side does not need a unique index — a child key is normally many-to-one — but it usually needs some index on its FK columns, because parent deletes and updates probe the child by that key. Most engines create the parent-side index automatically as part of the constraint and leave the child-side index to you; a missing one turns every parent delete into a full child scan and is a classic production surprise.

Diagnosing the error in practice

When you hit "no unique constraint matching given keys for referenced table", walk this list:

Is there any uniqueness on the parent columns at all, or just a plain index?
If there is a unique index, is it partial? Is it over an expression? Is it still building or invalid?
Is the uniqueness declared as a deferrable constraint?
Do the referenced columns match the key exactly, in the same order?
Are the types compatible (and comparable under the same collation)? A mismatch produces a different error but is often the real cause hiding nearby.

The design conclusion

This is the strongest practical argument for declaring uniqueness as a named constraint rather than as a bare index: constraints are the objects the rest of the system is allowed to cite. If you need conditional uniqueness (a partial index), accept that those columns cannot be a foreign-key target, and give the table a surrogate key that can be.

## Why the parent side must be a key Referential integrity says: every non-null child key value must match a parent row. For the engine to enforce that cheaply and unambiguously it needs two things: 1. **Uniqueness** — otherwise "the parent row" is not well defined. If two parents share the key, cascading updates and deletes have no single target, and the referential predicate degenerates into "at least one match", which the standard does not define. 2. **An access path** — the check runs on every child insert/update and, in the other direction, on every parent delete/update. Both need an index; a sequential scan per row would be unusable. A unique constraint gives both at once, which is why the standard phrases the rule in terms of constraints rather than indexes. ## What engines actually check When you add a foreign key, the engine looks for something on the parent table that proves uniqueness over the referenced column list. Implementations fall into three camps: **Standard-strict.** Only a `PRIMARY KEY` or `UNIQUE` constraint counts. A hand-built unique index is invisible to the check even though it enforces the same rule. This is the behaviour to assume if you want portable DDL. **Index-based.** The engine scans the parent's indexes for one that is unique, currently valid, **not partial**, built on **plain columns rather than expressions**, and **immediately checked** rather than deferrable. A bare `CREATE UNIQUE INDEX` therefore works here, but the exclusions bite in practice: - A **partial** unique index (`... WHERE deleted_at IS NULL`) guarantees uniqueness only over the filtered subset, so the FK cannot rely on it — and the rows it excludes are exactly the ones a child might point at. - An **expression** index on `lower(code)` keys on a derived value, not on `code`; two rows can share `lower(code)` casing variants — no, worse: the index proves `lower(code)` unique, which says nothing about `code` being unique as a lookup key by itself, and there is no way to match child values against it. - A **deferrable** unique constraint may be temporarily violated inside a transaction, so it cannot back a check that must hold at each statement. - An index still being built, or built and marked invalid after a failed concurrent build, does not count. **Lax.** A few engines only require *an* index whose leading columns are the referenced columns, unique or not. This is explicitly non-standard: it permits one child row to match several parent rows, and it makes cascade behaviour engine-specific. Do not rely on it. ## Column list, order and subsets The reference must name the **full** key column list. Given `UNIQUE (tenant_id, code)`, you cannot create a foreign key on `code` alone — `code` is not unique by itself, so the reference is meaningless. Referencing a superset fails too, because there is no key over those columns. Order matters for matching the key in engines that resolve by index: `REFERENCES lookup (tenant_id, code)` finds the key, while a different order may not, even though the constraint semantics would be equivalent. The child side does **not** need a unique index — a child key is normally many-to-one — but it usually needs *some* index on its FK columns, because parent deletes and updates probe the child by that key. Most engines create the parent-side index automatically as part of the constraint and leave the child-side index to you; a missing one turns every parent delete into a full child scan and is a classic production surprise. ## Diagnosing the error in practice When you hit "no unique constraint matching given keys for referenced table", walk this list: 1. Is there any uniqueness on the parent columns at all, or just a plain index? 2. If there is a unique index, is it partial? Is it over an expression? Is it still building or invalid? 3. Is the uniqueness declared as a **deferrable** constraint? 4. Do the referenced columns match the key exactly, in the same order? 5. Are the types compatible (and comparable under the same collation)? A mismatch produces a different error but is often the real cause hiding nearby. ## The design conclusion This is the strongest practical argument for declaring uniqueness as a named constraint rather than as a bare index: constraints are the objects the rest of the system is allowed to cite. If you *need* conditional uniqueness (a partial index), accept that those columns cannot be a foreign-key target, and give the table a surrogate key that can be.

The parent table legitimately needs conditional uniqueness — one active row per code, with retired rows kept. How do children reference it?
Give the parent a surrogate primary key (an identity or UUID column) and have children reference that instead. The partial unique index keeps enforcing the business rule over active rows, while the surrogate key provides the stable, whole-table-unique target a foreign key requires. This is the standard resolution and it also survives the code value being corrected later.
Does the child side of a foreign key need an index?
The constraint does not require one, but you almost always want it. Parent deletes and key updates must find referencing children, and without an index on the child's FK columns each of those becomes a full scan of the child table. It also matters for referential actions like ON DELETE CASCADE, which touch every matching child row.

saying these in an interview costs you the question

Thinking any index on the parent column is enough for a foreign key
Expecting a partial or filtered unique index to serve as an FK target
Referencing only part of a composite key and expecting it to resolve
Assuming the child FK columns are indexed automatically
Claiming the error means the child table is misconfigured rather than the parent

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Why the parent side must be a key

What engines actually check

Column list, order and subsets

Diagnosing the error in practice

The design conclusion

▾common follow-ups2 questions

▾red flags5 flags

§ context

▾related questions4 questions

▾related topics3 topics

Why the parent side must be a key

What engines actually check

Column list, order and subsets

Diagnosing the error in practice

The design conclusion

answer

go deeper

context