Thanks for the question.
Asked: August 11, 2016 - 2:48 pm UTC
Last updated: September 10, 2021 - 12:32 pm UTC
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 Connor 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> insert into par values (1);
1 row created.
SQL> insert into par values (2);
1 row created.
SQL> create table chd ( c int, p int references par(p));
Table created.
SQL> insert into chd values (1,1);
1 row created.
SQL> insert into chd values (2,2);
1 row created.
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> 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> delete from par where p = 2;
1 row deleted.
Rating
(6 ratings)
Is this answer out of date? If it is, please let us know via a Comment
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 !
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
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> 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> insert into parent values (10);
1 row created.
SQL> insert into child1 values (1,10);
1 row created.
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> set serverout on
SQL> declare
2 err varchar2(1000);
3 cons_viol exception;
4 pragma exception_init(cons_viol,-2292);
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);
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
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. :) :)
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
