42830ERRORTier 2 — Caution✅ HIGH confidence

invalid foreign key

Category: Syntax Error or Access Rule ViolationVersions: All Postgres versions

What this means

SQLSTATE 42830 is raised when a FOREIGN KEY constraint definition is structurally invalid — for example, the referenced columns do not form a unique or primary key in the referenced table, or the column counts do not match.

Why it happens

1The referenced columns in a FOREIGN KEY do not have a UNIQUE or PRIMARY KEY constraint in the referenced table
2The number of columns in the FOREIGN KEY differs from the number of referenced columns
3The data types of the foreign key columns do not match the referenced columns

How to reproduce

Creating a FK referencing a non-unique column.

trigger — this will ERROR

CREATE TABLE orders (
  order_id INT,
  customer_name TEXT,
  FOREIGN KEY (customer_name) REFERENCES customers(name)
  -- name has no UNIQUE constraint
);

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

Fix 1: Add a UNIQUE or PRIMARY KEY constraint to the referenced column

When the referenced column is not already unique.

fix

ALTER TABLE customers ADD CONSTRAINT customers_name_key UNIQUE (name);

Why this works

Foreign keys must reference a column (or column combination) with a UNIQUE or PRIMARY KEY constraint in the parent table.

Fix 2: Reference the primary key of the parent table instead

When the foreign key should reference the natural primary key.

fix

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Why this works

Primary keys are always unique and are the most common foreign key target.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 42 — Syntax Error or Access Rule Violation

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for invalid FK definitions. Stable across all versions.