deleting orphaned child records
Hi
Here is a small script that will help you delete orphaned child records when the parent keys are deleted.Useful when you are in development environment.
I have used a parent table called P and a child table called C.First we disable the refrential integrity constraint on C .Then delete the records in P. now run our script which produces the script to delete the records from the refrenced tables (child tables) run it and after that we can enable the constraint.
select * from p;
1
2
3
select * from c;
1 1
1 2
1 3
2 1
2 2
2 3
3 3
3 1
3 2
9 rows selected.
delete from p where p_x=1
delete from p where p_x=1
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.FK_C_CX) violated - child record found
alter table c disable constraint fk_c_cx
Table altered.
delete from p where p_x=1
select 'DELETE FROM '||a.table_name|| ' a '||CHR(10) ||
'WHERE NOT EXISTS '||CHR(10)||
'( SELECT 1 FROM ' ||CHR(10)
,b.table_name ||' b ' ||CHR(10)||
'WHERE a.'||a.column_name||'='||'b.'||b.column_name||');'
from user_cons_columns a,user_cons_columns b,user_constraints c
where c.constraint_name=a.constraint_name
and c.r_constraint_name=b.constraint_name
DELETE FROM C a
WHERE NOT EXISTS
( SELECT 1 FROM
P b
WHERE a.CX=b.P_X);
SQL> DELETE FROM C a
2 WHERE NOT EXISTS
3 ( SELECT 1 FROM
4 P b
5 WHERE a.CX=b.P_X);
3 rows deleted.
alter table c enable constraint fk_c_cx
Table altered.
Hope this helps
regards
Hrishy