PostgreSQL Error 23503 - Foreign Key Violation

What It Means

PostgreSQL error code 23503 (foreign_key_violation) occurs when an INSERT or UPDATE operation violates a foreign key constraint. This can happen in two directions:

1. Insert/Update in child table: Referencing a parent row that doesn’t exist.
2. Delete/Update in parent table: Trying to remove a parent row that is still referenced by child rows.

Common Causes

• Inserting a record with a foreign key referencing a non-existent parent
• Deleting a parent record that has dependent child records
• Inserting records in the wrong order (child before parent)
• Foreign key references an ID that was deleted
• Data imported without maintaining referential integrity
• Race condition: parent deleted between check and insert

How to Fix

Insert parent records first

-- Wrong order: child before parent
INSERT INTO orders (user_id, total) VALUES (999, 50.00);  -- User 999 doesn't exist!

-- Right order: parent first, then child
INSERT INTO users (id, name) VALUES (999, 'John');
INSERT INTO orders (user_id, total) VALUES (999, 50.00);

Add CASCADE rules

-- Drop and recreate constraint with CASCADE
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE      -- Auto-delete child rows
ON UPDATE CASCADE;     -- Auto-update foreign key values

-- Or use SET NULL
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL;

Handle in application code

// Node.js with pg
async function createOrder(userId, total) {
  try {
    const result = await pool.query(
      'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING *',
      [userId, total]
    );
    return result.rows[0];
  } catch (error) {
    if (error.code === '23503') {
      throw new Error(`User ${userId} does not exist`);
    }
    throw error;
  }
}

async function deleteUser(userId) {
  try {
    await pool.query('DELETE FROM users WHERE id = $1', [userId]);
  } catch (error) {
    if (error.code === '23503') {
      throw new Error('Cannot delete user: they have existing orders');
    }
    throw error;
  }
}

# Python with psycopg2
try:
    cursor.execute(
        "INSERT INTO orders (user_id, total) VALUES (%s, %s)",
        (user_id, total)
    )
    conn.commit()
except psycopg2.errors.ForeignKeyViolation as e:
    conn.rollback()
    print(f"Foreign key violation: {e}")

Delete child records first

-- Delete in the correct order when CASCADE isn't set
BEGIN;
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE user_id = 123);
DELETE FROM orders WHERE user_id = 123;
DELETE FROM users WHERE id = 123;
COMMIT;

Find dependent records

-- Find all foreign key constraints referencing a table
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 tc
JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND ccu.table_name = 'users';

Related Errors

• PostgreSQL 23505 - Unique violation: Duplicate key value, another constraint type.
• PostgreSQL 42P01 - Undefined table: The referenced table doesn’t exist.