2

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.

  • 2
    Why don't you define the foreign keys as on delete cascade, then you don't need to program anything. – a_horse_with_no_name Sep 8 '15 at 7:49
  • 1
    In the question you say you can't use ON DELETE CASCADE and in comments you say that you are not allowed to do data model changes. Yet in your code you are using ALTER TABLE statements and ON DELETE CASCADE. Isn't this contradicting? – ypercubeᵀᴹ Sep 10 '15 at 9:18
  • 1
    If this procedure is getting called frequently you certainly don't want to be doing any DDL in it. This will at best cause a slow down due to the locking (and by the way 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. – Colin 't Hart Sep 10 '15 at 9:30
  • You still haven't answered why you can't change the data model, or why using on delete cascade is not an option. – Colin 't Hart Sep 10 '15 at 14:16
  • 1)What is a "data model change"? Do you want to say you do not want to alter the database objects. So you do not want to issue DDL statement? 2) You delete a row using such a procedure and a lot of data will be deleted because you were not aware what references what ("I do not need to worry"). That is really funny. – miracle173 Sep 10 '15 at 17:31
2

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)

  • Thanks Ste Bov Actually my requirement is like it should dynamically delete the records from a table and then previous data is restored. As its a part of workflow and its getting called frequently. So, looking for something dynamic, because lets say after some months the again normalise one child and then increase the level of parent child. Hence,if we could write a generic code then it would be great. Please suggest some way. Thanks again – Sameer Pradhan Sep 8 '15 at 11:43
  • I might be able to give you an idea more of what I'm planning mid next week when I've had time to sit down with it. I'm confused what you mean by "dynamically delete the records from a table and then previous data is restored." the delete bit I kinda understand from the initial question but I'm not sure where the restore comes into it - Ste – Ste Bov Sep 8 '15 at 13:10
  • Thanks Ste Bov Sorry for confusion.I am trying to delete only. *restore : I was talking about requirement (not part of this question) "dynamically delete the records from a table" : irrespective of level of parent child tables and number of child tables in each level,it should delete. Hope this adds extra info to make a bit clear. – Sameer Pradhan Sep 8 '15 at 13:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.