Oracle ORA-02291 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-02291: integrity constraint violated - parent key not found

ORA-02291 is one of the most common referential integrity errors in Oracle databases. It occurs when you attempt to INSERT or UPDATE a row in a child table with a foreign key value that does not exist in the referenced parent table. Simply put, Oracle is enforcing referential integrity by preventing "orphan" child records from being created.

Top 3 Causes

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

The most frequent cause — trying to insert data into a child table while the referenced parent key simply doesn't exist yet.

-- Parent table: DEPARTMENTS
-- Child table: EMPLOYEES with FK on DEPT_ID

-- This will throw ORA-02291 if DEPT_ID 99 doesn't exist in DEPARTMENTS
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (5001, 'John Smith', 99);

-- Fix: Insert parent record first
INSERT INTO departments (dept_id, dept_name)
VALUES (99, 'New Division');

COMMIT;

-- Now the child insert will succeed
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (5001, 'John Smith', 99);

COMMIT;

2. Wrong Loading Order in Batch / ETL Jobs

In data migration or ETL pipelines, loading child tables before parent tables is a very common mistake that triggers this error at scale.

-- Check for orphan records in staging data BEFORE loading
SELECT s.order_id, s.customer_id
FROM orders_staging s
WHERE NOT EXISTS (
    SELECT 1
    FROM customers c
    WHERE c.customer_id = s.customer_id
);
-- If this returns rows, fix parent data first

-- Temporarily disable FK constraint for bulk load (use with caution)
ALTER TABLE orders DISABLE CONSTRAINT fk_orders_customer;

-- Perform bulk load
INSERT INTO orders SELECT * FROM orders_staging;

-- Re-enable after ensuring data integrity
ALTER TABLE orders ENABLE CONSTRAINT fk_orders_customer;

3. Data Type Mismatch or Whitespace Issues

Even if the parent key exists, a mismatch in data type, leading/trailing spaces, or case sensitivity can cause a lookup failure.

-- Parent stores '001' as VARCHAR2, child tries to insert numeric 1
-- This may fail due to implicit conversion issues
INSERT INTO order_lines (line_id, product_code)
VALUES (1, 1);  -- product_code '001' exists but numeric 1 does not match

-- Fix: Use explicit casting and TRIM
INSERT INTO order_lines (line_id, product_code)
VALUES (1, TRIM(TO_CHAR(1, '000')));  -- converts to '001'

-- Detect whitespace mismatches in parent table
SELECT '|' || product_code || '|' AS debug_value
FROM products
WHERE product_id = 100;

Quick Fix Solutions

-- 1. Verify the parent key exists before inserting
SELECT COUNT(*) FROM parent_table WHERE parent_id = :your_value;

-- 2. Find all orphan records causing issues
SELECT child.*
FROM child_table child
WHERE NOT EXISTS (
    SELECT 1 FROM parent_table p
    WHERE p.parent_id = child.parent_id
);

-- 3. Check FK constraint definition
SELECT constraint_name, r_constraint_name, status
FROM user_constraints
WHERE table_name = 'CHILD_TABLE'
AND constraint_type = 'R';

Prevention Tips

Validate before loading. Always run a pre-check query to confirm all foreign key values exist in the parent table before executing any bulk INSERT or migration script. Catching orphan records in staging is far cheaper than rolling back a failed production load.

Enforce load order. In ETL and batch jobs, always define a strict dependency-based loading sequence — parent tables must be fully committed before child tables are processed. Document this order and enforce it through your orchestration tool (e.g., Oracle Data Integrator, Apache Airflow).

-- Pre-migration validation template
SELECT 'FAILED - Orphan records exist' AS status, COUNT(*) AS cnt
FROM child_staging s
WHERE NOT EXISTS (SELECT 1 FROM parent_table p WHERE p.id = s.parent_id)
UNION ALL
SELECT 'OK - Ready to load', COUNT(*)
FROM child_staging s
WHERE EXISTS (SELECT 1 FROM parent_table p WHERE p.id = s.parent_id);

Related Errors

ORA-02292: Triggered when deleting a parent record that still has child records referencing it — the reverse of ORA-02291.
ORA-02298: Occurs when re-enabling a disabled FK constraint and existing data contains orphan child records.

📖 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