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.
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.
The error message will tell you which foreign key constraint failed and what value is missing. For example:
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.
Query the parent table to confirm the required row is present:
-- 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
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
-- 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:
-- 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.
When loading multiple related tables, always insert parents first:
-- 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:
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 hereFor large migrations, disable the constraint, load data, then re-enable:
-- 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 parentsUse this approach cautiously—only for one-time migrations where you control all the data.
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:
SET CONSTRAINTS ALL DEFERRED;
-- Your operations here
COMMIT; -- All constraints checked nowRESTRICT 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.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL