# 42830 — Invalid foreign key

> PostgreSQL SQLSTATE 42830: Invalid foreign key (invalid_foreign_key), source-backed diagnosis and recovery guidance.
---

# 42830 — Invalid foreign key

## At a glance {#at-a-glance}

`42830` is **invalid_foreign_key**: an FK definition cannot find a qualifying unique key on referenced columns. The case references a parent integer column with no unique constraint.

<!-- BEGIN SQLSTATE FACTS: generated by scripts/generate.py; do not edit -->

| Field | Value |
| --- | --- |
| SQLSTATE | `42830` |
| Condition | `invalid_foreign_key` |
| Status | `active` |
| Known present by | `7.4` |
| Locked snapshots | `9.0.23, 9.1.24, 9.2.24, 9.3.25, 9.4.26, 9.5.25, 9.6.24, 10.23, 11.22, 12.22, 13.23, 14.24, 15.19, 16.15, 17.11, 18.6, 19beta3` |
| Macros | `ERRCODE_INVALID_FOREIGN_KEY` |
| Aliases | `—` |

<!-- source facts: data/errcodes/42830.json -->
<!-- END SQLSTATE FACTS -->

## Meaning {#meaning}

Creation checks the parent key before any child row is inserted. The fixed primary is `there is no unique constraint matching given keys for referenced table "%s"`. For an ordinary FK, the referenced column set may match an eligible unique index in a different physical order; the source matcher rejects duplicate referenced columns and requires the right count, uniqueness, validity, and no partial predicate or index expressions. A matching deferrable unique/primary index takes a separate source-only `55000` path. This is definition-time, unlike `23503`, and the selected autocommit ALTER leaves `IDLE`.

## Diagnosis {#diagnosis}

Compare the referenced column set with `pg_constraint` and `pg_index`. Check for duplicate references, the number of key columns, unique/primary status, validity, partial predicates, and expressions; physical index order need not equal the FK list order for the ordinary path. Also check `indimmediate`: a matching deferrable key is rejected with `55000`, rather than the selected `42830`. Matching data types alone do not make a referenced key eligible.

## Response {#response}

Add or use an intentional non-deferrable unique key over the referenced column set, then create the FK. Check the business meaning of the parent table's key and desired NULL/MATCH semantics; adding an overly broad unique constraint can change accepted data. Do not reorder a valid index merely to mirror the FK syntax, and do not mistake a partial or expression index for a qualifying key. The case adds `UNIQUE (id)` then creates one FK. In an explicit transaction, the rejected `ALTER TABLE` leaves `INERROR`; roll back or return to a suitable savepoint before retrying. The selected autocommit path returns to `IDLE`.

## Observed diagnostics {#messages}

The selected `tablecmds.c` group is explicit `ERROR` with primary `there is no unique constraint matching given keys for referenced table "%s"` and no detail or hint. The same matcher has a source-only `55000` (`object_not_in_prerequisite_state`) variant, `cannot use a deferrable unique constraint for referenced table "%s"`, when the otherwise matching key is deferrable.

## Representative case {#case}

The registry creates parent and child tables, attempts the FK, adds the parent unique key, creates the valid FK, and counts it.

<!-- BEGIN SQLSTATE SNIPPET: fk_missing_unique_key -->

```sql
CREATE TABLE syntax_schema.fk_parent (id integer);
CREATE TABLE syntax_schema.fk_child (parent_id integer);
ALTER TABLE syntax_schema.fk_child ADD CONSTRAINT fk_bad FOREIGN KEY (parent_id) REFERENCES syntax_schema.fk_parent (id);
ALTER TABLE syntax_schema.fk_parent ADD CONSTRAINT fk_parent_id_key UNIQUE (id);
ALTER TABLE syntax_schema.fk_child ADD CONSTRAINT fk_good FOREIGN KEY (parent_id) REFERENCES syntax_schema.fk_parent (id);
SELECT count(*) FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace WHERE n.nspname = 'syntax_schema_name' AND c.conname = 'fk_good' AND c.contype = 'f';
```

<!-- END SQLSTATE SNIPPET -->

The selected 18.6 and 10.21 runs passed SQLSTATE, severity, state/recovery, repair, cleanup, and isolated-target stop assertions. See [`case JSON`](../data/cases/42830.json) and [`authored evidence`](../data/evidence/42830.json); private manifest and registry hashes are recorded there.

## Versions {#versions}

The locked catalogue contains this condition from the 7.4 presence bound through the listed snapshots. The selected natural case passed on PostgreSQL 18.6 and 10.21; that bounded result does not infer every intermediate release or every source branch.

## Related {#related}

- [`23503`](../23503/)
- [`23514`](../23514/)

## Sources {#sources}

- `src.errcodes.REL_18_6` — fixed definition at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; SHA-256 `6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/utils/errcodes.txt#L1-L1)).
- `src.invalid-fk.18.6` — `src/backend/commands/tablecmds.c` lines 13639–13642 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L13639-L13642)).
- `src.invalid-fk.10.23` — `src/backend/commands/tablecmds.c` lines 8133–8136 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `6de441c88496c6cf57a836898388085ec08bf69afd70d07acdafd6089b9b5f8c` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/commands/tablecmds.c#L8133-L8136)).
- `src.invalid-fk-guards.18.6` — complete `transformFkeyCheckAttrs` in `src/backend/commands/tablecmds.c` lines 13505–13642 at `724edf9bde9d356724ad384a2e196edc3c9f80f7`; blob SHA-256 `422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9` ([source](https://github.com/postgres/postgres/blob/724edf9bde9d356724ad384a2e196edc3c9f80f7/src/backend/commands/tablecmds.c#L13505-L13642)).
- `src.invalid-fk-guards.10.23` — complete `transformFkeyCheckAttrs` in `src/backend/commands/tablecmds.c` lines 8002–8136 at `02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4`; blob SHA-256 `6de441c88496c6cf57a836898388085ec08bf69afd70d07acdafd6089b9b5f8c` ([source](https://github.com/postgres/postgres/blob/02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4/src/backend/commands/tablecmds.c#L8002-L8136)).
- `src.calls.REL_18_6` / `src.calls.REL_10_23` — fixed call scans, SHA-256 `9ee8a0e81d8f0825c5c1ae45583439859a26e602bdd4ce2f2a62aa278867ccbf` / `00d16d3eb01b71ccf1b245c8f3102f9d0ec9f36fb02777b8dd1b99fcb263040c`.
- `manifest.42830` / `snippet-registry.42830` — hashes are recorded in `evidence/42830.json` and each runtime record.