Oracle Error ORA-02292

Error code

ORA-02292

Product

Oracle Database

Versions

Oracle Database 19c, 21c, 23ai and 26ai (same wording)

Category

Constraints & Integrity

Last reviewed

24 October 2026

What it means

ORA-02292 means a DELETE (or an UPDATE of a key value) on a parent table would leave rows in a child table pointing at a key that no longer exists, so Oracle rejected the statement.

A foreign key protects child rows from losing their parent. Oracle's reference gives the cause as an attempt to delete a parent key value that has a foreign key dependency. Unless the foreign key was declared with an ON DELETE clause, Oracle does not allow you to delete referenced key values that have dependent rows, and the statement is rolled back. Changing a referenced key value with UPDATE is blocked in the same way.

The constraint named in the message is the foreign key on the child table, not the parent's primary key. Look it up to find which child table still holds rows. A parent table can be referenced by several child tables; the message names only one constraint, so check them all before retrying. Related errors: dropping a referenced table needs CASCADE CONSTRAINTS, and TRUNCATE of a parent table with an enabled foreign key raises a different error.

Common causes

• A DELETE removes a parent row (a customer, a product) that still has child rows (orders, order lines).
• A cleanup or archive job deletes parent tables before child tables.
• An UPDATE changes a primary or unique key value that child rows reference.
• A table has several child tables and only some were cleaned up before the parent delete.
• A self-referencing table (for example employees and their manager) deletes a row that other rows point to.

Diagnose it

Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.

SELECT c.table_name AS child_table, cc.column_name AS child_column,
       c.delete_rule, p.table_name AS parent_table
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
JOIN all_constraints p
  ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
WHERE c.owner = 'SHOP'
  AND c.constraint_name = 'ORDERS_CUSTOMER_FK'
ORDER BY cc.position;

SELECT c.owner, c.table_name AS child_table, c.constraint_name, c.delete_rule, c.status
FROM all_constraints c
JOIN all_constraints p
  ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
WHERE c.constraint_type = 'R'
  AND p.owner = 'SHOP'
  AND p.table_name = 'CUSTOMERS';

SELECT customer_id, COUNT(*) AS child_rows
FROM shop.orders
WHERE customer_id = 42  -- the parent key you tried to delete
GROUP BY customer_id;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

DELETE FROM shop.order_lines
WHERE order_id IN (SELECT id FROM shop.orders WHERE customer_id = 42);

DELETE FROM shop.orders WHERE customer_id = 42;

DELETE FROM shop.customers WHERE id = 42;

UPDATE shop.customers
SET status = 'CLOSED'
WHERE id = 42;

ALTER TABLE shop.order_lines DROP CONSTRAINT order_lines_order_fk;

ALTER TABLE shop.order_lines ADD CONSTRAINT order_lines_order_fk
  FOREIGN KEY (order_id) REFERENCES shop.orders (id)
  ON DELETE CASCADE;

Examples

Deleting a customer who still has orders

Not reproduced here: no Oracle Database is installed for this site. The result shows the message format documented in Oracle's error reference, with this example's constraint name filled in (the tables are assumed to be in schema SHOP).

CREATE TABLE customers (id NUMBER PRIMARY KEY, email VARCHAR2(200));
CREATE TABLE orders (
    id          NUMBER PRIMARY KEY,
    customer_id NUMBER CONSTRAINT orders_customer_fk REFERENCES customers (id)
);

INSERT INTO customers VALUES (42, 'sara@example.com');
INSERT INTO orders VALUES (1001, 42);

DELETE FROM customers WHERE id = 42;

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

Changing a key value that child rows reference

Not reproduced here: no Oracle Database is installed for this site. The message format comes from Oracle's error reference and the names are illustrative. Surrogate keys that never change avoid this case entirely.

-- renumbering a customer whose orders still use the old id
UPDATE customers SET id = 4200 WHERE id = 42;

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

How to prevent it

Decide for each foreign key what should happen when a parent goes away (block, cascade or set null) and declare it when the table is created. Write cleanup and archive jobs to work from child tables up to parent tables.

Use surrogate keys that never change, so no UPDATE ever has to touch a referenced key. Prefer marking important parents as inactive over deleting them, and handle ORA-02292 in application code as "this item is still in use".

Common questions

Learn more on CodeWithSQL

• Primary key vs foreign key
• SQL constraints explained

Official documentation

• ORA-02292 (Oracle Database Error Messages)
• Constraint clause: ON DELETE (Oracle SQL Language Reference 19c)
• ALL_CONSTRAINTS (Oracle Database Reference 19c)

Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.