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