How to fix ERROR: foreign key constraint violation in PostgreSQL

PostgreSQLINTERMEDIATEHIGHUpdated Dec 25, 2025· 250 views

A foreign key constraint violation occurs when you try to insert, update, or delete a row that breaks the relationship between parent and child tables. The fix depends on whether you're inserting orphaned records, deleting referenced rows, or loading data in the wrong order.

Quick fix

Fixes 23503

ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
  1. Check the exact error message for the missing parent value
  2. Verify the parent record exists
  3. For DELETE violations: handle existing child references
  4. For bulk loads: order inserts correctly and use transactions
postgresql — error
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".
ERROR: foreign key constraint violation

What this error means

A foreign key constraint ensures referential integrity between tables by requiring that values in a child table match values in a parent table. When PostgreSQL detects a violation—such as inserting a child row whose foreign key value doesn't exist in the parent table, or deleting a parent row that still has child references—it raises this error. The error typically includes a detailed message like: "Key (city)=(Berkeley) is not present in table cities." This tells you exactly which column and value caused the violation.

Typical symptoms

  • INSERT or UPDATE statements fail on child tables with a 23503 error code
  • DELETE statements fail when trying to remove parent rows that have child references
  • Bulk data loads or migrations fail partway through
  • Race conditions in concurrent transactions where one session deletes a parent while another tries to insert a child

Common causes

  1. Inserting or updating a child row with a foreign key value that doesn't exist in the parent table
  2. Deleting a parent row while child rows still reference it (when ON DELETE CASCADE or similar is not configured)
  3. Inserting child records before their parent records exist, or in the wrong order during bulk loads
  4. Data type mismatches between the foreign key column and the referenced primary key
  5. Concurrent transactions modifying related rows without proper transaction isolation or locking

How to fix "ERROR: foreign key constraint violation"

1Check the exact error message for the missing parent value

The error message will tell you which foreign key constraint failed and what value is missing. For example:

bash
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey"
DETAIL: Key (city)=(Berkeley) is not present in table "cities".

Note the constraint name (weather_city_fkey), the column (city), and the missing value (Berkeley). This tells you exactly what to fix.

2Verify the parent record exists

Query the parent table to confirm the required row is present:

sql
-- Check if the parent record exists
SELECT * FROM cities WHERE id = 5;
-- Or for the example above:
SELECT * FROM cities WHERE city_name = 'Berkeley';

If the parent record doesn't exist, either:
- Insert the missing parent record first, then retry the child insert
- Correct the foreign key value in your child record to reference an existing parent

3For DELETE violations: handle existing child references

If you're deleting a parent row and the error says child rows still reference it, choose one approach:

Option A: Delete child rows first

sql
-- Delete all child records pointing to this parent
DELETE FROM weather WHERE city_id = 5;
-- Then delete the parent
DELETE FROM cities WHERE id = 5;

Option B: Add ON DELETE CASCADE to the constraint
If you frequently delete parents, modify the foreign key constraint:

sql
-- Drop the old constraint
ALTER TABLE weather DROP CONSTRAINT weather_city_fkey;

-- Recreate with CASCADE
ALTER TABLE weather
ADD CONSTRAINT weather_city_fkey
FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE;

Use CASCADE only when child records should always be deleted when their parent is deleted.

4For bulk loads: order inserts correctly and use transactions

When loading multiple related tables, always insert parents first:

sql
-- Correct order: parent first, then child
BEGIN;
  INSERT INTO cities (id, city_name) VALUES (5, 'Berkeley');
  INSERT INTO weather (city_id, temperature) VALUES (5, 72);
COMMIT;

Or use deferred constraint checking within a transaction:

sql
BEGIN;
  SET CONSTRAINTS weather_city_fkey DEFERRED;

  -- Now you can insert in any order
  INSERT INTO weather (city_id, temperature) VALUES (5, 72);
  INSERT INTO cities (id, city_name) VALUES (5, 'Berkeley');

COMMIT; -- Constraints validated here
5For ETL or data import: temporarily disable and re-check constraints

For large migrations, disable the constraint, load data, then re-enable:

sql
-- Disable the constraint
ALTER TABLE weather DISABLE TRIGGER ALL;

-- Load your data
COPY weather FROM '/path/to/data.csv' WITH (FORMAT csv);

-- Re-enable and validate
ALTER TABLE weather ENABLE TRIGGER ALL;

-- Find any remaining violations
SELECT w.* FROM weather w
LEFT JOIN cities c ON w.city_id = c.id
WHERE c.id IS NULL;

-- Delete orphaned records or insert missing parents

Use this approach cautiously—only for one-time migrations where you control all the data.

Advanced notes

Deferred Constraints vs Immediate: By default, PostgreSQL checks constraints immediately. Use SET CONSTRAINTS to defer checking until transaction commit, allowing multi-step operations to work. Example:

sql
SET CONSTRAINTS ALL DEFERRED;
-- Your operations here
COMMIT; -- All constraints checked now

RESTRICT vs NO ACTION: Both prevent deletion, but NO ACTION can be deferred. RESTRICT checks immediately.

Error Code 23503: This is SQLSTATE 23503 in PostgreSQL error codes, making it easy to handle in application error handling.

Cascade Options: Beyond CASCADE, you can use SET NULL (sets FK to NULL on parent delete) or SET DEFAULT (uses default value). Choose based on your data model.

Frequently asked questions

What does "23503" mean?
A foreign key constraint ensures referential integrity between tables by requiring that values in a child table match values in a parent table. When PostgreSQL detects a violation—such as inserting a child row whose foreign key value doesn't exist in the parent table, or deleting a parent row that still has child references—it raises this error. " This tells you exactly which column and value caused the violation.
How do I fix "23503"?
A foreign key constraint violation occurs when you try to insert, update, or delete a row that breaks the relationship between parent and child tables. The fix depends on whether you're inserting orphaned records, deleting referenced rows, or loading data in the wrong order.
What causes "23503"?
Inserting or updating a child row with a foreign key value that doesn't exist in the parent table Deleting a parent row while child rows still reference it (when ON DELETE CASCADE or similar is not configured) Inserting child records before their parent records exist, or in the wrong order during bulk loads
Is the "23503" error serious?
Yes. This is a high-severity error that usually blocks work until it is fixed, but the steps above resolve it.

Official resources & further reading