Delete cascade - PL/SQL version

Lets suppose that:

• foreign key constraints are created without "CASCADE" option enabled


• all primary keys are simple numeric(19) fields named 'ID'


• no cyclic references (i.e. tableA -> tableB and tableB -> tableA)

You got request from developer to delete "one problematic row" in XY table.
Unfortunately, your row is referenced by multiple child rows in other tables, those child rows are referenced by other rows and so on, and so on ...
This scenario is not the worst one if you made the database design. But if you are just
simple DBA on complicated project, this can spoil your daily routine.

create or replace procedure delete_cascade( table_name_ in varchar2, id_ in numeric ) is
/*
 Generates set of DELETE statements which have to be executed prior to final entry deletion.
 
 Stored procedure have to be invoked with parameters:
  TABLE_NAME_ : name of the table from which record have to be deleted
  ID_ : id of the record
 
 Stored procedure assumes:
  - that all primary keys are named 'ID' and that pk's data type is 'NUMERIC'
  - that cycle references (i.e. tableA -> tableB and tableB -> tableA) do not exist
  
 Author: Milos Babic 
 
 */
    TYPE refcur IS REF CURSOR;
    
    childid_ numeric(19);
    fetch_sql_ varchar2(2000);
    cur_child_tables refcur;
    
    cursor ref_tables is 
        select fk.table_name as child_table_name_, fk_c.column_name as child_column_name_
        from  user_constraints fk, 
          user_constraints pk, 
          user_cons_columns fk_c 
        where fk.constraint_type='R' and fk.r_constraint_name=pk.constraint_name 
        and pk.table_name=table_name_ and fk_c.constraint_name=fk.constraint_name;
        
begin

    for iterator in ref_tables loop
    
  fetch_sql_ := 'select id from ' || iterator.child_table_name_ || ' where  ' || iterator.child_column_name_ || '=' || id_;

        open cur_child_tables for fetch_sql_;
        
        loop 
            fetch cur_child_tables into childid_;
            exit when cur_child_tables%notfound;
            delete_cascade ( iterator.child_table_name_, childid_ );
        end loop;
        
        close cur_child_tables;
   
        insert into delete_log values (delete_log_seq.nextval, 'delete from ' || iterator.child_table_name_ || ' where ' || iterator.child_column_name_ || ' = ' || id_);
 
   end loop;
    
end delete_cascade;

This procedure expects that you have created table DELETE_LOG and sequence DELETE_LOG_SEQ:

create sequence delete_log_seq;
create table delete_log (
    id numeric(19),
    statement varchar2(2000)
);

Upon successful execution, you can obtain list of delete statements from DELETE_LOG table.

select statement from delete_log order by id

In case you want to try, this is sample DB

create table a (
    id numeric(19),
    a_log varchar2(100),
    constraint pk_a primary key (id)
) tablespace user_data;


create table a_a (
    id numeric(19),
    a_id numeric(19),
    a_a_log varchar2(100),
    constraint pk_a_a primary key (id)
) tablespace user_data;
alter table a_a
   add constraint fk_a_a_a foreign key (a_id)
      references a (id)
      initially immediate deferrable;


create table a_b (
    id numeric(19),
    a_id numeric(19),
    a_b_log varchar2(100),
    constraint pk_a_b primary key (id)
) tablespace user_data;
alter table a_b
   add constraint fk_a_b_a foreign key (a_id)
      references a (id)
      initially immediate deferrable;

create table a_a_a (
    id numeric(19),
    a_a_id numeric(19),
    a_a_a_log varchar2(100),
    constraint pk_a_a_a primary key (id)
) tablespace user_data;
alter table a_a_a
   add constraint fk_a_a_a_a foreign key (a_a_id)
      references a_a (id)
      initially immediate deferrable;


  insert into a values (1,'1');
  insert into a values (2,'2');
  insert into a values (3,'3');
  
  insert into a_a values (1,1,'1');
  insert into a_a values (2,1,'1');
  insert into a_a values (3,2,'1');
  insert into a_a values (4,3,'1');

  insert into a_b values (1,1,'1');
  insert into a_b values (2,1,'1');
  insert into a_b values (3,2,'1');
  insert into a_b values (4,3,'1');
  
  
  insert into a_a_a values (1,1,'1');
  insert into a_a_a values (2,1,'1');
  insert into a_a_a values (3,2,'1');
  insert into a_a_a values (4,2,'1');