9 Replies Latest reply: Jan 25, 2013 5:05 AM by Purvesh K RSS

    procedure requirement

    RajeshKanna
      Hi,

      I have Created three tables as script as below
      create table two(deptno number(5) constraint two_dno_pk primary key);
      
      insert into two values(10);
      
      insert into two values(20);
      
      insert into two values(30);
      
      
      create table one(eno number(5) constraint one_eno_pk primary key,
                       dno number(5) constraint one_dno_fk references two(deptno));
      
      insert into one values(1,10);
      
      insert into one values(2,10);
      
      insert into one values(3,20);
      
      
      create table three(eno number(5) constraint three_eno_fk references one(eno));
      
      insert into three values(1);
      
      insert into three values(1);
      
      insert into three values(2);
      
      commit;
      My Requirement is create a procedure to pass the parameter as a tablename, delete records from all its dependent tables
      and the parent table. ( Its nothing but Recursive delete)

      can any one help me writing the procedure ..?

      Cheers,
      Rajesh
        • 1. Re: procedure requirement
          Purvesh K
          Use an ON DELETE CASCADE while specifying the Foreign Key constraint.

          Just like this:
          dno number(5) constraint one_dno_fk references two(deptno) on delete cascade
          If you have already created the constraints, you will have to drop and re-create those with cascade option.

          Once you have created constraint with this option, all you need to do is, Delete data from Parent and Child data shall be deleted automatically.
          • 2. Re: procedure requirement
            RajeshKanna
            In my table on delete cascade option is not there..without altering the table any possibilty is there
            • 3. Re: procedure requirement
              NSK2KSN
              you don't need a procedure for that,

              you just add on delete cascade on your foriegn key definition, if you delete the parent record your child records will also be deleted
              /* Formatted on 1/25/2013 2:53:11 PM (QP5 v5.215.12089.38647) */
              CREATE TABLE two (deptno NUMBER (5) CONSTRAINT two_dno_pk PRIMARY KEY);
              
              INSERT INTO two
                   VALUES (10);
              
              INSERT INTO two
                   VALUES (20);
              
              INSERT INTO two
                   VALUES (30);
              
              
              CREATE TABLE one
              (
                 eno   NUMBER (5) CONSTRAINT one_eno_pk PRIMARY KEY,
                 dno   NUMBER (5) CONSTRAINT one_dno_fk REFERENCES two (deptno) on delete cascade
              );
              
              INSERT INTO one
                   VALUES (1, 10);
              
              INSERT INTO one
                   VALUES (2, 10);
              
              INSERT INTO one
                   VALUES (3, 20);
              
              
              CREATE TABLE three
              (
                 eno   NUMBER (5) CONSTRAINT three_eno_fk REFERENCES one (eno) on delete cascade
              );
              
              INSERT INTO three
                   VALUES (1);
              
              INSERT INTO three
                   VALUES (1);
              
              INSERT INTO three
                   VALUES (2);
              
              COMMIT;
              
               delete from two where deptno = 10;
              
              this will delete related records from two, one and three tables.
              • 4. Re: procedure requirement
                Purvesh K
                RajeshKanna wrote:
                In my table on delete cascade option is not there..without altering the table any possibilty is there
                If you cannot alter the table, write a procedure that will do the job for you.
                Procedure will be nothing but a bunch of SQL statements, that shall start Deleting the records from Table at lowest hierarchy of relationship, and progress upwards till it reaches the Root i.e. parent table.

                IMV, the Alter statements will be better than having a procedure to perform the job.

                It is very much like re-inventing the wheel.
                • 5. Re: procedure requirement
                  RajeshKanna
                  Can u help me the writing select statement
                  • 6. Re: procedure requirement
                    Purvesh K
                    RajeshKanna wrote:
                    Can u help me the writing select statement
                    What select statement?

                    Aren;t you talking of Deletes?
                    • 7. Re: procedure requirement
                      Paul  Horth
                      RajeshKanna wrote:
                      In my table on delete cascade option is not there..without altering the table any possibilty is there
                      Why can't you alter the constraints?
                      • 8. Re: procedure requirement
                        RajeshKanna
                        Hi purvesh,

                        Child table to parent table relation ships usnig connect by level in my requirement
                        • 9. Re: procedure requirement
                          Purvesh K
                          Although not recommended, I did provide a solution on similar grounds, which included maintaining a Configuration table for list of parents and child.

                          I am unable to find that post, so shall post if I find it. But, that does not change the grounds of reason to implement such a solution. Especially when you have an option of achieving without so much of trouble i.e. using Foreign Key cascading constraints feature.