How to write a procedure which is replacement of ON DELETE CASCADE

oracleplsql

I want to make write a procedure which will delete rows from parent table as well as child tables. How to achieve that.? In my case I can not to use on delete cascade (No data model changes).

If the child tables has further child the single procedure should be able to delete them also.

Note:

  1. I can not do any kind of data model changes.
  2. As this procedure is getting called frequently in my case, it needs to be faster also.
  3. I am looking for a generic procedure. So if any of the child tables is normalized in future then also I do not need to worry.
  4. It should delete any number of parent child levels. Parent–>Child–>Child ….(n times).
    We need to extend it to any level deletion (more flexible).

I tried the following:

create table supplier
( supplier_id numeric(10) not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  constraint supplier_pk primary key (supplier_id)
);

create table products
( product_id numeric(10) not null,
  supplier_id numeric(10) not null,
  constraint fk_supplier
    foreign key (supplier_id)
    references supplier(supplier_id)
);

insert into supplier  values (1,'Test','Test');
insert into products  values (111,1);

create or replace PROCEDURE PARENT_CHILD_DELETE 
(
  PV_TABLE_NAME_IN IN VARCHAR2 
, PV_PRIMARY_COLS_IN IN VARCHAR2
, PV_WHERE_CLAUSE_IN IN VARCHAR2 DEFAULT '' 
) AS 
  --declare section
  lv_drop_sql varchar2(30000);
  lv_add_cons_sql lv_drop_sql%type;
  lv_del_sql lv_drop_sql%type;
  lv_rem_cons_sql lv_drop_sql%type;

BEGIN
  --loop for all the child tables
  for lv_childs_cur in (select constraint_name,table_name
                            from  all_constraints 
                        where  r_constraint_name in
                               (select  constraint_name
                                  from  all_constraints
                                where table_name= PV_TABLE_NAME_IN))
  loop
    --execute for each child tables  
    --drop the constraints
    lv_drop_sql := 'ALTER TABLE ' || lv_childs_cur.table_name ||
                   ' DROP CONSTRAINT '|| lv_childs_cur.constraint_name;
    execute immediate lv_drop_sql;

    --add on delete cascade
    lv_add_cons_sql := ' ALTER TABLE ' || lv_childs_cur.table_name ||
                       ' ADD CONSTRAINT ' || lv_childs_cur.constraint_name ||
                       ' FOREIGN KEY ('||PV_PRIMARY_COLS_IN||')
                         REFERENCES supplier('||PV_PRIMARY_COLS_IN||')
                         ON DELETE CASCADE ';
    execute immediate lv_add_cons_sql;
  end loop;

  --delete from parent table
  lv_del_sql := 'DELETE FROM '||PV_TABLE_NAME_IN||' '||PV_WHERE_CLAUSE_IN;
  execute immediate lv_del_sql;


  --loop for all the child tables
  for lv_childs_cur in (select constraint_name,table_name
                           from all_constraints 
                         where  r_constraint_name in
                           (select  constraint_name
                              from  all_constraints
                            where table_name= PV_TABLE_NAME_IN))
  loop
    --execute for each child tables  
    --drop the constraints
    lv_drop_sql := 'ALTER TABLE ' || lv_childs_cur.table_name ||
                   ' DROP CONSTRAINT '|| lv_childs_cur.constraint_name;
    execute immediate lv_drop_sql;

    --add on delete cascade
    lv_rem_cons_sql := ' ALTER TABLE ' || lv_childs_cur.table_name ||
                       ' ADD CONSTRAINT ' || lv_childs_cur.constraint_name ||
                       ' FOREIGN KEY ('||PV_PRIMARY_COLS_IN||')
                         REFERENCES supplier('||PV_PRIMARY_COLS_IN||')';
    execute immediate lv_rem_cons_sql;
  end loop;

END PARENT_CHILD_DELETE;


DECLARE
  PV_TABLE_NAME_IN VARCHAR2(200);
  PV_WHERE_CLAUSE_IN VARCHAR2(200);
  PV_PRIMARY_COLS_IN VARCHAR2(200);
BEGIN
  PV_TABLE_NAME_IN := 'SUPPLIER';
  PV_PRIMARY_COLS_IN := 'supplier_id';
  PV_WHERE_CLAUSE_IN := 'where supplier_id = 1';

  PARENT_CHILD_DELETE(
    PV_TABLE_NAME_IN => PV_TABLE_NAME_IN,
    PV_PRIMARY_COLS_IN => PV_PRIMARY_COLS_IN,
    PV_WHERE_CLAUSE_IN => PV_WHERE_CLAUSE_IN
  );
END;

Now its deleting but if it works ONLY FOR 1 LEVEL parent child. However I need to extend to any level. Please help.

Inputs from Commentators:

As the procedure I wrote is changing data model for sometime,though
I am trying to alter again. This clearly contradicts my requirement(No data model changes).
it will commit any work done prior to this procedure being called) and at worst cause locking contention
and even deadlocks if multiple users can be invoking this procedure near-simultaneously.

Hence, this procedure is of course is not a choice. Kindly provide any alternate solution.

Best Answer

Your best bet is to delete on cascade but that's not always an option

Making a procedure to dynamically work out what columns exist and which relate to one another is not an easy task even if you do have all primary / foreign key relationships setup.

I'd advise sitting down looking at where everything is and how it all relates, looking from the main table then each one of them in turn and delete based on the content of the first table to the second, second to the third and so on, you'll need to get the data from the first -> second -> third level in that order, but delete in the order third -> second -> first

Its painstaking but it seems to be the best way rather than trying to essentially write an AI program in sql. (unless someone knows a really neat trick that I'm unaware of)

I've got a similar process scheduled on my list of things to do next week, not going to lie, not looking forward to it.

Ste

psudo code for what I'm doing

input clientID to procedure
get customers relating to client
get transactions from customers
get items based on transactions
remove all items
remove all transactions
remove customers
remove client

Warning, make sure that the procedure is secured away and locked so nobody can use it by accident, we're having the procedure content commented out and everyone denyed access to it so someone must go in, edit the proc and then run it (we're only going to run it possibly twice in the next year, I don't know how often you intend to use yours)

Related Question