PostgreSQL Invalid Foreign Key (SQLSTATE 42830)

PostgreSQL raises ERROR: there is no unique constraint matching given keys for referenced table "<table>" with SQLSTATE 42830 and condition name invalid_foreign_key when a FOREIGN KEY constraint definition is structurally invalid. This is a DDL-time error — it occurs when creating or altering a table, not when inserting or updating data.

What This Error Means

SQLSTATE 42830 belongs to error class 42 — "Syntax Error or Access Rule Violation". Despite the class name, this particular error is semantic rather than syntactic: the SQL parsed successfully, but the constraint cannot be created because the referenced column or column set does not satisfy PostgreSQL's referential integrity requirements.

For a foreign key to be valid, the referenced column (or combination of columns in a composite FK) must be covered by a PRIMARY KEY or a UNIQUE constraint on the referenced table. PostgreSQL enforces this at constraint-creation time. If no such constraint exists, the server rejects the FOREIGN KEY definition entirely and raises 42830.

Because this error occurs during DDL execution (a CREATE TABLE or ALTER TABLE statement), no data is affected. The statement is rolled back, and the constraint is not created. Any surrounding transaction remains open and in a normal state — unlike some runtime errors that abort the transaction.

Common Causes

1. Referencing a column with no unique constraint. The most common cause: the referenced column exists but has neither a PRIMARY KEY nor a UNIQUE constraint defined on it.

2. Referencing a subset of a composite unique constraint. If the referenced table has a multi-column UNIQUE (a, b) constraint, a foreign key that references only column a (not the full set (a, b)) is invalid, because a alone is not guaranteed to be unique.

3. Referencing a column in a partitioned table without a matching unique index. Partitioned tables have stricter requirements — a UNIQUE or PRIMARY KEY constraint on a partitioned table must include all partition key columns. A foreign key referencing a partitioned table column that is not covered by such a constraint will fail with 42830.

4. Creating the foreign key before the referenced table's constraint is added. In migration scripts that run out of order, the FOREIGN KEY definition may be processed before the PRIMARY KEY or UNIQUE constraint on the referenced table is in place.

5. Typo in the referenced column name causing a fallback to a non-unique column. A misspelled column name may resolve to a different column that lacks the required constraint, producing this error instead of a "column does not exist" error in some cases.

How to Fix invalid_foreign_key

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

   -- If the referenced column should be a primary key:
   ALTER TABLE referenced_table ADD PRIMARY KEY (id);
   
   -- If it should be unique but not the primary key:
   ALTER TABLE referenced_table ADD CONSTRAINT uq_referenced_col UNIQUE (col_name);
   

   After adding the constraint, re-run the failing CREATE TABLE or ALTER TABLE statement.

2. Reference the full column set of a composite unique constraint.

   If the referenced table has UNIQUE (tenant_id, user_id), your foreign key must reference both columns:

   ALTER TABLE orders
     ADD CONSTRAINT fk_orders_user
     FOREIGN KEY (tenant_id, user_id)
     REFERENCES users (tenant_id, user_id);
   

3. Verify the constraint exists before defining the foreign key in migration scripts.

   In deployment pipelines, ensure the step that adds the PRIMARY KEY or UNIQUE constraint on the referenced table runs before the step that adds the foreign key. Use \d referenced_table in psql or query pg_constraint to confirm:

   SELECT conname, contype
   FROM pg_constraint
   WHERE conrelid = 'referenced_table'::regclass
     AND contype IN ('p', 'u');
   

4. Check the referenced column name for typos.

   Confirm the exact column name and its constraints:

   SELECT column_name, data_type
   FROM information_schema.columns
   WHERE table_name = 'referenced_table';
   

Additional Information

• SQLSTATE 42830 has been part of PostgreSQL's error catalog since at least PostgreSQL 7.4. The behavior has not changed meaningfully across versions.
• Related SQLSTATE codes in class 42: 42501 (insufficient_privilege), 42601 (syntax_error), 42P01 (undefined_table), 42703 (undefined_column). These may appear alongside 42830 in migration scripts that have multiple structural problems.
• Most PostgreSQL client drivers (libpq, psycopg2, asyncpg, JDBC) surface this as a ProgrammingError or SQLState exception with code 42830. ORMs like SQLAlchemy raise sqlalchemy.exc.ProgrammingError; Django raises django.db.ProgrammingError.
• This error has no runtime performance implications — it is a pure DDL-time check. It does not affect query plans or connection health.
• When using CREATE TABLE ... LIKE or schema-dump/restore workflows, foreign keys are typically emitted after all tables and their constraints. If restoring a partial dump, missing constraints on referenced tables will trigger 42830 for each dependent foreign key.

Frequently Asked Questions

Why does PostgreSQL require the referenced column to be unique? Referential integrity requires that every foreign key value in the child table maps to exactly one row in the parent table. If the referenced column were not unique, a single FK value could match multiple parent rows, making it impossible to define a canonical referenced row. PostgreSQL enforces uniqueness at constraint-creation time rather than leaving the ambiguity to query time.

Can I reference a unique index instead of a unique constraint? No. PostgreSQL requires a PRIMARY KEY or UNIQUE constraint — a unique index created with CREATE UNIQUE INDEX is not sufficient for a foreign key reference. You must define the constraint with ADD CONSTRAINT ... UNIQUE (...) or ADD PRIMARY KEY (...).

This error appeared after I upgraded PostgreSQL — what changed? Upgrades themselves do not introduce 42830 on existing valid schemas. If you see it after an upgrade, it is most likely because a migration script is running that references a table whose constraints were not yet created, or because a pg_dump/pg_restore operation is replaying DDL out of order. Check whether the constraint on the referenced table is present before the FK is created.

How is 42830 different from 23503 (foreign_key_violation)? 42830 is a DDL error raised when defining a foreign key constraint that references a non-unique column. 23503 is a DML error raised at runtime when an INSERT or UPDATE would place a value in a FK column that has no matching row in the referenced table. They occur at entirely different stages and have different remedies.