12 Replies Latest reply: Aug 7, 2012 5:39 AM by Purvesh K RSS

    Delete Records in Tables

    fame
      Hi all,
      I have Doubt in Delete a record in tables,
      I want to delete a record in one table, but that record reference to one or more tables,
      how can i delete those records.

      For Example,
      T_A table refer in the T_B table and T_B table refer in the T_C table.


      Regards,
      Fame
        • 1. Re: Delete Records in Tables
          O.Developer
          HI

          This is called - Master -details relations or Primary-master key constrains.

          You cannot delete data from master while related data exists in details table.

          If you want so,

          First delete from master the relevant data , then you can delete from master.
          • 2. Re: Delete Records in Tables
            Purvesh K
            fame wrote:
            Hi all,
            I have Doubt in Delete a record in tables,
            I want to delete a record in one table, but that record reference to one or more tables,
            how can i delete those records.

            For Example,
            T_A table refer in the T_B table and T_B table refer in the T_C table.


            Regards,
            Fame
            I think the Table design in incorrect. If you had implemented Foreign Keys (Sounds like you have not from your explanation), a Simple delete from Master table would have deleted all the dependent references.

            You will have to now:
            1. Check for the Depth of Dependency.
            2. Pursue a Bottom - Up approach starting with the Table having no dependency and moving to the Top.

            If you can now, read the Example of On Delete Constraint and implement it, you can still save ample efforts and improve database design.
            • 3. Re: Delete Records in Tables
              Purvesh K
              AppsLearner wrote:
              HI

              This is called - Master -details relations or Primary-master key constrains.

              You cannot delete data from master while related data exists in details table.
              Untrue, unless you have implemented Foreign Keys. Without Foreign Key, you can still delete the Master first and Child Later (Definitely a wrong approach :) )
              If you want so,

              First delete from master the relevant data , then you can delete from master.
              Could not make up what you wanted to convey!!!
              • 4. Re: Delete Records in Tables
                Karthick_Arp
                If you want your child records to be deleted automatically you need to define the foreign key constraint as ON DELETE CASCADE

                Example.
                SQL> create table parent (id integer primary key);
                 
                Table created.
                 
                SQL> create table child (id integer, constraint child_fk foreign key (id) references parent(id) on delete cascade);
                 
                Table created.
                 
                SQL> insert into parent values (1);
                 
                1 row created.
                 
                SQL> insert into child values (1);
                 
                1 row created.
                 
                SQL> select * from parent;
                 
                        ID
                ----------
                         1
                 
                SQL> select * from child;
                 
                        ID
                ----------
                         1
                 
                SQL> delete from parent;
                 
                1 row deleted.
                 
                SQL> select * from parent;
                 
                no rows selected
                 
                SQL> select * from child;
                 
                no rows selected
                 
                SQL> 
                • 5. Re: Delete Records in Tables
                  fame
                  Hi,
                  i don't want to use delete on cascade,
                  Plz tell another way.

                  Regards,
                  Fame
                  • 6. Re: Delete Records in Tables
                    Purvesh K
                    Follow my suggestion then.
                    Write an entire procedure to perform the job, which can be achieved in a Single Statement in each of your tables. Plus, you will have to maintain and enhance the procedure to accommodate any more extension to the child tables.

                    Reason for doing this is, You are not informing Oracle about the Relationship between the Tables by not forming the Foreign Key. Hence, it would be base-less to expect an Oracle implementation that shall do the Subjected job for you.

                    By the way, what prevents you from using the Foreign Key relationships? Is the Master data not a Primary Key? Are the Tables not normalized?
                    • 7. Re: Delete Records in Tables
                      fame
                      Can you show example procedure of this one.
                      • 8. Re: Delete Records in Tables
                        Ashu_Neo
                        See if you don't want to use "On delete cascade" ( The best way of having for these purposes), then try to disable the constraint dynamically and before delete from all tables in PROC. There is no other way around in oracle for this.
                        • 9. Re: Delete Records in Tables
                          BS2012
                          As everyone told, this is like parent-child relationship. You can not delete that parent record without deleting the child records. The other way if you disable that constraint which is a bridge between these two tables then you can delete the parent record without deleting the child records...

                          *"on delete cascade"* is another option and it deletes the child record automatically, when you delete the parent ones.


                          Bibhuprasad

                          Edited by: Bibhuprasad on Aug 7, 2012 3:00 PM

                          Edited by: Bibhuprasad on Aug 7, 2012 3:01 PM
                          • 10. Re: Delete Records in Tables
                            Purvesh K
                            fame wrote:
                            Can you show example procedure of this one.
                            Can't help you with a ready code now. but a small algorithm to work with.

                            1. Loop through all the Tables in Hierarchy
                            2. Get the Foreign/Mapping Key for Parent and Child Table.
                            3. Delete the Data of Child.
                            4. Check if it is the End of Hierarchy. If yes, then Delete the Root (Main Table) tables data. If not Follow Step 2.

                            I do understand, it is very easy to prepare an Algorithm compared to its implementation. Hence, for this reason I asked you to use the Foreign Key Constraints at the First Place. :)

                            You still haven't answered by previous few questions.
                            • 11. Re: Delete Records in Tables
                              fame
                              How can i find the last hierarchy of the loop.
                              • 12. Re: Delete Records in Tables
                                Purvesh K
                                fame wrote:
                                How can i find the last hierarchy of the loop.
                                If I did not have any PK - FK relationship, I would think of using a similar SQL to get the Deepest Hierarchy.
                                select distinct b.column_name, b.table_name, decode (
                                                          b.table_name,
                                                            'TABLE3', 1,
                                                            'TABLE2', 2,
                                                            'TABLE1', 3
                                                         ) hie
                                          from user_constraints a,
                                               user_cons_columns b
                                         where a.constraint_name = b.constraint_name
                                           and a.constraint_type = 'P'
                                           and b.table_name in ('TABLE1', 'TABLE2', 'TABLE3')
                                         order by decode (
                                                          b.table_name,
                                                            'TABLE3', 1,
                                                            'TABLE2', 2,
                                                            'TABLE1', 3
                                                         );