Skip to Main Content
  • Questions
  • ORA-02292: integrity constraint <constraint name> violated

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question.

Asked: August 11, 2016 - 2:48 pm UTC

Answered by: Connor McDonald - Last updated: July 27, 2020 - 2:23 am UTC

Category: Database - Version: 12 c

Viewed 10K+ times! This question is

You Asked

Hello,

I am attempting to delete a record / object (that may use several tables at the DB level) from an application.
The delete statement is raising a
ORA-02292: integrity constraint <constraint name> violated

How can I find the Delete statement (DML) being executed?
so that I can find the record in the table
so that I can finally find the referenced record ?

Thank you !

and we said...

This means you tried to delete a row in a parent table where a child row still existed.

So, use the constraint name to locate the columns, and the query them.

eg


SQL> create table par ( p int primary key );

Table created.

SQL>
SQL> insert into par values (1);

1 row created.

SQL> insert into par values (2);

1 row created.

SQL>
SQL> create table chd ( c int, p int references par(p));

Table created.

SQL>
SQL> insert into chd values (1,1);

1 row created.

SQL> insert into chd values (2,2);

1 row created.

SQL>
SQL> delete from par where p = 2;
delete from par where p = 2
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0021402) violated - child record found


SQL>
SQL>
SQL> select * from user_cons_columns
  2  where constraint_name = 'SYS_C0021402';

OWNER
------------------------------
CONSTRAINT_NAME
--------------------------------------------------------------------------------------------------------------------------------
TABLE_NAME                     COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
MCDONAC
SYS_C0021402
CHD                            P                                       1


1 row selected.

SQL> delete from chd
  2  where p in (
  3    select p from par
  4    where p = 2 );

1 row deleted.

SQL>
SQL> delete from par where p = 2;

1 row deleted.

SQL>
SQL>
SQL>


and you rated our response

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Useful

August 12, 2016 - 10:49 am UTC

Reviewer: A reader

Thank you Mr. McDonald for your prompt reply,

This is what I have been doing for simple Delete statements, where the table and record were obvious or could be determined Par (table) PK = 2 (record).

In may case, this is a complex objects, spanning several tables (unknown names).
I don't see the Delete statement executed by the application.
The error is raised on first FK conflict; but there may be several FK conflicts.

This leads me to the same question:
How can I find the Delete statement (DML) being executed?

because I don't know the (table name) par or the (record) p = 2; so I can't write a statement like:
SQL> delete from chd
2 where p in (
3 select p from par
4 where p = 2 );

Would be great if ORA-02292
would also say
ORA-02292: integrity constraint <constraint name> violated
where parent.p = 2

Hopes this makes may challange more clear.

Thank you for your great work for the community !
Connor McDonald

Followup  

August 14, 2016 - 5:37 am UTC

You do "know" the table name, because xxx_CONSTRAINTS also contains the R_CONSTRAINT_NAME, which can be joined back to xxx_CONSTRAINTS to determine the table name.

August 03, 2017 - 1:02 pm UTC

Reviewer: A reader

When User delete record front-end side then following error occure. but i want to give my own message to front-end when error comes.
how it is possible from back-end side.

System.Data.OracleClient.OracleException: ORA-02292: integrity constraint (USERDEMO.FKSTOCKMAS_PRODUCTID) violated - child record found


Connor McDonald

Followup  

August 04, 2017 - 2:02 am UTC

You can just parse the error text and query the dictionary, eg

SQL> create table parent ( p int primary key );

Table created.

SQL> create table child1 ( c int, p int references parent(p));

Table created.

SQL> create table child2 ( c int, p int references parent(p));

Table created.

SQL>
SQL> insert into parent values (1);

1 row created.

SQL> insert into child1 values (1,1);

1 row created.

SQL> insert into child2 values (1,1);

1 row created.

SQL>
SQL> insert into parent values (10);

1 row created.

SQL> insert into child1 values (1,10);

1 row created.

SQL>
SQL> delete from parent where p = 10;
delete from parent where p = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0031304) violated - child record found


SQL>
SQL> set serverout on
SQL> declare
  2    err varchar2(1000);
  3    cons_viol exception;
  4    pragma exception_init(cons_viol,-2292);
  5
  6    own varchar2(200);
  7    tab varchar2(200);
  8  begin
  9    delete from parent where p = 10;
 10  exception
 11    when cons_viol then
 12      err := substr(sqlerrm,34);
 13
 14      select owner, table_name
 15      into   own,tab
 16      from   all_constraints
 17      where  owner = substr(err,1,instr(err,'.')-1)
 18      and    constraint_name = substr(err,instr(err,'.')+1,instr(err,')')-instr(err,'.')-1 );
 19      raise_application_error(-20000,'You still have some records in '||own||'.'||tab);
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-20000: You still have some records in MCDONAC.CHILD1
ORA-06512: at line 19


Need PL/SQL Procedure to delete all corresponding child records given parent record

July 04, 2020 - 10:22 am UTC

Reviewer: Enthusiast from India

Hello,


I am attempting to delete a record / object (that may use several tables at the DB level) from an application.
The delete statement is raising a
ORA-02292: integrity constraint <constraint name> violated

In my scenario , parent table have multiple child tables, and each child can have multiple children , grand-children, and grand-children can have even more grand-children. It means multi-level hierarchy where same child can also have multiple parents.

So can you pleas suggest/provide any procedure which takes input as parent table primary key value and deletes its all corresponding child values and at the end parent value as well.

For ex, in current case, I will give delProcedure(par.p=2) and it will delete records from chd.p=2 1st and then par.p=2.

I want this to happen recursively for all children, grand-children, grand-grand-children for a parent.

Hope it clears my requirement. !!

Thank you in advance. :) :)
Connor McDonald

Followup  

July 06, 2020 - 5:44 am UTC

Well... I googled "recursive foreign key delete" and got this as my first hit

https://clarodba.wordpress.com/2018/03/26/a-script-to-delete-records-recursively-from-all-child-tables/

Need PL/SQL Procedure to delete all corresponding child records given parent record

July 07, 2020 - 7:25 pm UTC

Reviewer: Enthusiast from India

Great Thanks Tom!!!! :) :)

Though I have not tested suggested script yet, but when I went through it to understand , it seems the script will give me desired result.




Connor McDonald

Followup  

July 08, 2020 - 6:41 am UTC

Please keep us posted on how successful you were.

Need PL/SQL Procedure to delete all corresponding child records given parent record

July 25, 2020 - 1:18 pm UTC

Reviewer: Enthusiast from India

Hi Tom,

The above solution worked perfectly fine for me. Thanks for providing it.

The only point which I required to take care was, to delete records from those tables which are not related by referential integrity constraint but by design they contain related data. I used my environment specific information to achieve it.

Once again would like to thank you from bottom of my heart :D :D


Connor McDonald

Followup  

July 27, 2020 - 2:23 am UTC

glad it all worked out