PostgreSQL 23503 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 23503: Foreign Key Violation

PostgreSQL error code 23503 occurs when a foreign key constraint is violated, meaning the referential integrity between two tables has been broken. This happens either when you try to insert a child record that references a non-existent parent, or when you attempt to delete a parent record that still has dependent child rows. Understanding the root cause quickly is essential since this error blocks your transactions entirely.

Top 3 Causes

1. Inserting a Child Record with a Non-Existent Parent Key

The most common cause. You're trying to INSERT into a child table with a foreign key value that doesn't exist in the parent table yet.

-- This will fail if customer_id 9999 doesn't exist in customers
INSERT INTO orders (order_id, customer_id, amount)
VALUES (1001, 9999, 250.00);
-- ERROR:  insert or update on table "orders" violates foreign key constraint
-- DETAIL: Key (customer_id)=(9999) is not present in table "customers".

-- Fix: Insert the parent record first, then the child
BEGIN;
  INSERT INTO customers (id, name, email)
  VALUES (9999, 'John Doe', 'john@example.com');

  INSERT INTO orders (order_id, customer_id, amount)
  VALUES (1001, 9999, 250.00);
COMMIT;

2. Deleting a Parent Record That Still Has Child References

When you try to DELETE a parent row while child rows still reference it, PostgreSQL blocks the operation to protect referential integrity.

-- This will fail if orders still reference customer 9999
DELETE FROM customers WHERE id = 9999;
-- ERROR:  update or delete on table "customers" violates foreign key constraint
-- DETAIL: Key (id)=(9999) is still referenced from table "orders".

-- Fix Option A: Delete children first, then the parent
BEGIN;
  DELETE FROM orders WHERE customer_id = 9999;
  DELETE FROM customers WHERE id = 9999;
COMMIT;

-- Fix Option B: Use ON DELETE CASCADE (if schema change is allowed)
ALTER TABLE orders
  DROP CONSTRAINT orders_customer_id_fkey;

ALTER TABLE orders
  ADD CONSTRAINT orders_customer_id_fkey
  FOREIGN KEY (customer_id)
  REFERENCES customers(id)
  ON DELETE CASCADE;

3. Wrong Insertion Order During Data Migration or Bulk Load

During ETL jobs or bulk data migrations, child table data is loaded before the corresponding parent data exists, triggering mass foreign key violations.

-- Temporarily disable FK checks for bulk loading (requires superuser)
SET session_replication_role = 'replica';

COPY customers FROM '/data/customers.csv' CSV HEADER;
COPY orders    FROM '/data/orders.csv'    CSV HEADER;

-- Re-enable FK enforcement
SET session_replication_role = 'DEFAULT';

-- Validate referential integrity after load
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;
-- No rows = data is clean

Quick Fix Solutions

-- Check which FK constraint is violated
SELECT
    tc.constraint_name,
    tc.table_name        AS child_table,
    kcu.column_name      AS child_column,
    ccu.table_name       AS parent_table,
    ccu.column_name      AS parent_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_name = 'orders';  -- replace with your table name

-- Find orphaned child records quickly
SELECT o.*
FROM orders o
WHERE NOT EXISTS (
    SELECT 1 FROM customers c WHERE c.id = o.customer_id
);

Prevention Tips

1. Always wrap parent-child operations in a single transaction.
Define and enforce insertion order (parent → child) and deletion order (child → parent) at the application level. Wrapping related operations in a transaction ensures atomicity and prevents partial states from triggering FK violations.

2. Define explicit ON DELETE / ON UPDATE policies at design time.
Choose the right referential action for your business rules during schema design — don't leave it to runtime guesswork.

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount      NUMERIC(10,2),
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
        ON DELETE RESTRICT   -- block delete if child rows exist
        ON UPDATE CASCADE    -- propagate parent PK changes automatically
);

Setting these policies explicitly reduces unexpected runtime errors and documents your data integrity rules directly in the schema, where every developer can see them.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

DEV Community