ORA-02292 integrity constraint violated solution and commands

The error "ORA-02292: integrity constraint violated - child record found" occurs in Oracle databases when you try to delete or update a parent row that has child rows referencing it in another table through a foreign key constraint. This typically means there are dependent records in a child table that prevent the action.

Here’s how you can resolve the issue:

1. Identify the foreign key constraint and tables involved

You need to know which foreign key is causing the error and which child table holds the dependent rows.

Run this query to identify the foreign key and child table:

SELECT a.constraint_name, a.table_name, a.column_name, c_pk.table_name AS parent_table
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND c_pk.table_name = 'PARENT_TABLE_NAME'; -- Replace with the actual parent table name

2. Delete the child rows first

If you want to delete the parent record, you need to delete the corresponding child records first from the child table. For example:

DELETE FROM child_table_name WHERE foreign_key_column = parent_key_value;

After deleting the child rows, you can delete the parent row:

DELETE FROM parent_table_name WHERE parent_key_column = parent_key_value;

3. Use ON DELETE CASCADE (optional)

If you frequently need to delete parent records and want to automatically delete related child records, you can modify the foreign key constraint to include the ON DELETE CASCADE option. This way, deleting a parent record will automatically delete related child records:

ALTER TABLE child_table_name
ADD CONSTRAINT fk_name FOREIGN KEY (foreign_key_column)
REFERENCES parent_table_name (parent_key_column)
ON DELETE CASCADE;

4. Disable Foreign Key Constraint (not recommended in most cases)

If you want to temporarily disable the foreign key constraint to allow deleting parent records without dealing with the child records, you can disable the foreign key constraint. However, this is risky, as it may lead to orphaned records in the child table:

ALTER TABLE child_table_name DISABLE CONSTRAINT fk_name;

After disabling, you can delete the parent row, but remember to enable the constraint again after the operation.

ALTER TABLE child_table_name ENABLE CONSTRAINT fk_name;

5. Truncate Child Table (if acceptable):

If you don’t care about the child data and want to remove all records in one go, you can truncate the child table.

TRUNCATE TABLE child_table_name;

However, truncating a table cannot be undone, so use it with caution.

Example scenario:

Assume you have two tables: orders (parent) and order_items (child), with a foreign key order_items.order_id referencing orders.id.

If you try to delete a record from orders without deleting the related records in order_items, you'll get the ORA-02292 error.

To solve this, delete the related rows from order_items first:

DELETE FROM order_items WHERE order_id = 101;

Then delete the record from orders:

DELETE FROM orders WHERE id = 101;

This should resolve the issue.