📋

Description

The ORA-02292 error in Oracle Database indicates an attempt to delete a parent record that has existing child records referencing it through a foreign key constraint. This error prevents data integrity issues by ensuring referential integrity.

💬

Error Message

ORA-02292: integrity constraint ( string . string ) violated - child record found

🔍

Known Causes

3 known causes

⚠️

Foreign Key Dependency

A child table contains foreign key values that reference the parent table's primary key value you are trying to delete. 💻

⚠️

Missing Cascade Delete

The foreign key constraint does not have the `ON DELETE CASCADE` option enabled, which would automatically delete child records. ⚙

⚠️

Incorrect Deletion Order

You are attempting to delete the parent record before deleting the associated child records. 🌐

🛠️

Solutions

4 solutions available

1. Delete Child Records First easy

Remove dependent records before deleting parent

1

Find and delete child records

-- Find children:
SELECT * FROM orders WHERE customer_id = 100;

-- Delete children first:
DELETE FROM orders WHERE customer_id = 100;

-- Now delete parent:
DELETE FROM customers WHERE customer_id = 100;

2. Use ON DELETE CASCADE medium

Modify FK to automatically delete children

1

Add CASCADE option to FK

-- Drop existing constraint
ALTER TABLE orders DROP CONSTRAINT fk_customer;

-- Recreate with CASCADE
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;

-- Now deleting parent auto-deletes children:
DELETE FROM customers WHERE customer_id = 100;
-- All orders for this customer are also deleted

3. Use ON DELETE SET NULL medium

Set child FK to NULL instead of deleting

1

Modify FK to set NULL

-- FK column must be nullable
ALTER TABLE orders MODIFY customer_id NULL;

-- Add SET NULL constraint
ALTER TABLE orders ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL;

-- Deleting parent sets children's FK to NULL:
DELETE FROM customers WHERE customer_id = 100;
-- orders.customer_id becomes NULL for affected rows

4. Find All Child Tables medium

Identify all tables referencing the parent

1

Query constraints to find dependencies

-- Find all FKs referencing customers table
SELECT a.table_name AS child_table,
       a.constraint_name,
       b.table_name AS parent_table
FROM user_constraints a
JOIN user_constraints b
  ON a.r_constraint_name = b.constraint_name
WHERE b.table_name = 'CUSTOMERS'
AND a.constraint_type = 'R';

2

Delete from all child tables

-- Delete from all dependent tables in correct order
DELETE FROM order_items WHERE order_id IN
  (SELECT order_id FROM orders WHERE customer_id = 100);
DELETE FROM orders WHERE customer_id = 100;
DELETE FROM customer_addresses WHERE customer_id = 100;
DELETE FROM customers WHERE customer_id = 100;

Oracle ORA-02292: Child Record Found