42830 — Invalid foreign key

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.

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

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

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

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

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

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';

The selected 18.6 and 10.21 runs passed SQLSTATE, severity, state/recovery, repair, cleanup, and isolated-target stop assertions. See case JSON and authored evidence; private manifest and registry hashes are recorded there.

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

• 23503
• 23514

Sources

• src.errcodes.REL_18_6 — fixed definition at 724edf9bde9d356724ad384a2e196edc3c9f80f7; SHA-256 6e8de346643ba84aa3c9c6a73360acfc7b2dfb89162c06c08ce9bf5bcd5bbcba (source).
• src.invalid-fk.18.6 — src/backend/commands/tablecmds.c lines 13639–13642 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9 (source).
• src.invalid-fk.10.23 — src/backend/commands/tablecmds.c lines 8133–8136 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 6de441c88496c6cf57a836898388085ec08bf69afd70d07acdafd6089b9b5f8c (source).
• src.invalid-fk-guards.18.6 — complete transformFkeyCheckAttrs in src/backend/commands/tablecmds.c lines 13505–13642 at 724edf9bde9d356724ad384a2e196edc3c9f80f7; blob SHA-256 422dc8e833df4940755973c757e338295822ba3e4c7266c40669f49f96eb15a9 (source).
• src.invalid-fk-guards.10.23 — complete transformFkeyCheckAttrs in src/backend/commands/tablecmds.c lines 8002–8136 at 02991e79f8f58bc208f05dcc8af0c62dbe0a6ea4; blob SHA-256 6de441c88496c6cf57a836898388085ec08bf69afd70d07acdafd6089b9b5f8c (source).
• 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.