EC
42830
PostgreSQLERRORNotableSyntax Error or Access Rule ViolationHIGH confidence

invalid foreign key

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
  1. 1The referenced columns in a FOREIGN KEY do not have a UNIQUE or PRIMARY KEY constraint in the referenced table
  2. 2The number of columns in the FOREIGN KEY differs from the number of referenced columns
  3. 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
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
);

expected output

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 When the referenced column is not already unique.

Add a UNIQUE or PRIMARY KEY constraint to the referenced column
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 When the foreign key should reference the natural primary key.

Reference the primary key of the parent table instead
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 documentation ↗

Class 42 — Syntax Error or Access Rule Violation

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors