ORA-02292 Integrity Constraint Violated Child Record Found - Complete Resolution Guide

ORA-02292: Integrity Constraint Violated - Child Record Found

Error Overview

Error Text: ORA-02292: integrity constraint (SCHEMA.CONSTRAINT_NAME) violated - child record found

The ORA-02292 error occurs when you attempt to delete or update a parent record that has dependent child records in another table. This is Oracle’s referential integrity enforcement preventing orphaned records.

Understanding the Error

Parent Table (departments)          Child Table (employees)
+---------------+                   +------------------+
| department_id | <---FK--------    | department_id    |
| dept_name     |     |             | employee_id      |
+---------------+     |             | emp_name         |
                      |             +------------------+
                      |
        Deleting from parent fails if children exist

Common Causes

1. Deleting Parent Records

• Attempting to delete a row referenced by child table
• Bulk delete operations hitting FK constraints
• Cleanup scripts ignoring dependencies

2. Updating Primary Key Values

• Changing a primary key value that’s referenced
• Key migrations or data corrections

3. Cascading Dependency Chains

• Multiple levels of parent-child relationships
• Complex foreign key networks

Error Examples and Solutions

Example 1: Basic Delete Failure

-- Setup: Parent-child relationship
CREATE TABLE departments (
  dept_id NUMBER PRIMARY KEY,
  dept_name VARCHAR2(50)
);

CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  dept_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

-- Insert data
INSERT INTO departments VALUES (10, 'Engineering');
INSERT INTO employees VALUES (1, 'John Doe', 10);

-- ERROR: Try to delete parent with existing child
DELETE FROM departments WHERE dept_id = 10;
-- ORA-02292: integrity constraint (HR.FK_EMP_DEPT) violated - child record found

-- SOLUTION 1: Delete children first
DELETE FROM employees WHERE dept_id = 10;
DELETE FROM departments WHERE dept_id = 10;

-- SOLUTION 2: Update children to NULL (if allowed)
UPDATE employees SET dept_id = NULL WHERE dept_id = 10;
DELETE FROM departments WHERE dept_id = 10;

-- SOLUTION 3: Update children to different parent
UPDATE employees SET dept_id = 20 WHERE dept_id = 10;
DELETE FROM departments WHERE dept_id = 10;

Example 2: Updating Primary Key

-- ERROR: Changing a referenced primary key
UPDATE departments SET dept_id = 100 WHERE dept_id = 10;
-- ORA-02292: integrity constraint violated - child record found

-- SOLUTION 1: Update children first, then parent
UPDATE employees SET dept_id = 100 WHERE dept_id = 10;
UPDATE departments SET dept_id = 100 WHERE dept_id = 10;
-- Note: This only works if no constraint prevents the child update

-- SOLUTION 2: Insert new, migrate, delete old
INSERT INTO departments VALUES (100, 'Engineering');
UPDATE employees SET dept_id = 100 WHERE dept_id = 10;
DELETE FROM departments WHERE dept_id = 10;

Example 3: Using ON DELETE CASCADE

-- Create FK with cascade delete (prevent ORA-02292)
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  dept_id NUMBER,
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON DELETE CASCADE  -- Automatically delete children
);

-- Now this works - children are deleted automatically
DELETE FROM departments WHERE dept_id = 10;
-- Employees with dept_id = 10 are also deleted

-- WARNING: Use CASCADE carefully - data loss is automatic

Example 4: Using ON DELETE SET NULL

-- Create FK that sets NULL on parent delete
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  dept_id NUMBER,  -- Must be nullable
  CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
    REFERENCES departments(dept_id)
    ON DELETE SET NULL  -- Set to NULL when parent deleted
);

-- Now this works - children get NULL in FK column
DELETE FROM departments WHERE dept_id = 10;
-- Employees with dept_id = 10 now have dept_id = NULL

Diagnostic Queries

Find Child Records Preventing Delete

-- Find which constraint is violated (from error message)
-- Then find the child table and column
SELECT
  c.table_name as child_table,
  cc.column_name as child_column,
  c.r_constraint_name as parent_constraint,
  p.table_name as parent_table
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
JOIN user_constraints p ON c.r_constraint_name = p.constraint_name
WHERE c.constraint_name = 'FK_EMP_DEPT';  -- From error message

Find All Child Records

-- Dynamic query to find child records
-- Replace values from diagnostic query above
SELECT COUNT(*) as child_count
FROM employees  -- child_table from above
WHERE dept_id = 10;  -- parent key value you're trying to delete

Map All Foreign Key Dependencies

-- Find all FKs referencing a table
SELECT
  c.constraint_name,
  c.table_name as child_table,
  cc.column_name as child_column,
  c.delete_rule
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND c.r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = 'DEPARTMENTS'  -- Your parent table
      AND constraint_type = 'P'
  )
ORDER BY c.table_name;

Generate Delete Script for Dependencies

-- Generate SQL to delete dependent records
SELECT 'DELETE FROM ' || c.table_name ||
       ' WHERE ' || cc.column_name || ' = :parent_value;' as delete_stmt
FROM user_constraints c
JOIN user_cons_columns cc ON c.constraint_name = cc.constraint_name
WHERE c.constraint_type = 'R'
  AND c.r_constraint_name IN (
    SELECT constraint_name
    FROM user_constraints
    WHERE table_name = 'DEPARTMENTS'
  );

Resolution Strategies

Strategy 1: Delete Children First (Safe)

-- Manual cascading delete
BEGIN
  -- Level 3: Delete grandchildren first
  DELETE FROM order_items WHERE order_id IN (
    SELECT order_id FROM orders WHERE customer_id = :cust_id
  );

  -- Level 2: Delete children
  DELETE FROM orders WHERE customer_id = :cust_id;

  -- Level 1: Delete parent
  DELETE FROM customers WHERE customer_id = :cust_id;

  COMMIT;
END;
/

Strategy 2: Temporarily Disable Constraint

-- Disable constraint (use with caution!)
ALTER TABLE employees DISABLE CONSTRAINT fk_emp_dept;

-- Perform delete
DELETE FROM departments WHERE dept_id = 10;

-- Clean up orphaned children
DELETE FROM employees WHERE dept_id = 10;  -- Or handle appropriately

-- Re-enable constraint
ALTER TABLE employees ENABLE CONSTRAINT fk_emp_dept;

-- WARNING: May fail to enable if orphaned records exist

Strategy 3: Modify Constraint to CASCADE

-- Drop existing constraint
ALTER TABLE employees DROP CONSTRAINT fk_emp_dept;

-- Recreate with CASCADE
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
ON DELETE CASCADE;

-- Now delete will cascade automatically
DELETE FROM departments WHERE dept_id = 10;

Strategy 4: Use Deferred Constraints

-- Create deferrable constraint
ALTER TABLE employees ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
DEFERRABLE INITIALLY IMMEDIATE;

-- In transaction, defer checking
SET CONSTRAINT fk_emp_dept DEFERRED;

-- Now you can delete parent, then children
DELETE FROM departments WHERE dept_id = 10;
DELETE FROM employees WHERE dept_id = 10;

COMMIT;  -- Constraint checked here

Handling Complex Dependency Chains

Multi-Level Dependencies

-- Example: Customers -> Orders -> Order_Items -> Item_Details

-- Create procedure to handle cascading deletes
CREATE OR REPLACE PROCEDURE delete_customer(p_customer_id NUMBER) AS
BEGIN
  -- Delete from deepest level up
  DELETE FROM item_details
  WHERE order_item_id IN (
    SELECT order_item_id FROM order_items
    WHERE order_id IN (
      SELECT order_id FROM orders WHERE customer_id = p_customer_id
    )
  );

  DELETE FROM order_items
  WHERE order_id IN (
    SELECT order_id FROM orders WHERE customer_id = p_customer_id
  );

  DELETE FROM orders WHERE customer_id = p_customer_id;

  DELETE FROM customers WHERE customer_id = p_customer_id;

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

Self-Referencing Tables

-- Employee table with manager FK
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(100),
  manager_id NUMBER,
  CONSTRAINT fk_emp_manager FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);

-- ERROR: Delete employee who is someone's manager
DELETE FROM employees WHERE emp_id = 100;
-- ORA-02292: child records exist (subordinates)

-- SOLUTION: Update subordinates first
UPDATE employees SET manager_id = NULL WHERE manager_id = 100;
DELETE FROM employees WHERE emp_id = 100;

-- Or reassign to different manager
UPDATE employees SET manager_id = 200 WHERE manager_id = 100;
DELETE FROM employees WHERE emp_id = 100;

Prevention Strategies

1. Design with Cascade Rules

-- When creating tables, consider delete behavior
CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  CONSTRAINT fk_ord_cust FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE CASCADE  -- Use when children should be deleted
);

CREATE TABLE employee_assignments (
  assignment_id NUMBER PRIMARY KEY,
  emp_id NUMBER,
  CONSTRAINT fk_assign_emp FOREIGN KEY (emp_id)
    REFERENCES employees(emp_id)
    ON DELETE SET NULL  -- Use when children can exist without parent
);

2. Create Delete Helper Procedures

-- Encapsulate delete logic
CREATE OR REPLACE PROCEDURE safe_delete_department(p_dept_id NUMBER) AS
  v_emp_count NUMBER;
BEGIN
  -- Check for dependent records
  SELECT COUNT(*) INTO v_emp_count
  FROM employees WHERE dept_id = p_dept_id;

  IF v_emp_count > 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Cannot delete department. ' || v_emp_count || ' employees assigned.');
  END IF;

  DELETE FROM departments WHERE dept_id = p_dept_id;
  COMMIT;
END;
/

3. Use Application-Level Checks

-- Check dependencies before attempting delete
SELECT
  'Cannot delete: ' || COUNT(*) || ' employees in this department'
FROM employees
WHERE dept_id = :dept_id
HAVING COUNT(*) > 0;

Quick Reference: ON DELETE Options

Option	Behavior	Use Case
NO ACTION	Error on delete (default)	Strict data integrity
CASCADE	Delete children automatically	Log entries, session data
SET NULL	Set FK to NULL	Optional relationships

Related Errors

• ORA-02291 - Integrity constraint violated - parent key not found
• ORA-02290 - Check constraint violated
• ORA-00001 - Unique constraint violated
• ORA-02449 - Unique/primary keys in table referenced by FKs

Summary

1. ORA-02292 protects data integrity - Child records would be orphaned
2. Delete children before parents - Or update FK to NULL/different value
3. Use ON DELETE CASCADE - When children should be automatically deleted
4. Use ON DELETE SET NULL - When children can exist independently
5. Map dependencies first - Know your FK chain before bulk deletes
6. Create helper procedures - Encapsulate complex delete logic
7. Consider soft deletes - Flag records instead of physical deletion