1 2 Previous Next 16 Replies Latest reply: Jan 17, 2013 4:04 AM by jeneesh RSS

    Deleting record on basis of master table ID

    Christy H.
      Hello,
      SQL> desc news
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                   NOT NULL NUMBER(14)
       NEWS_DATE                                          TIMESTAMP(0)
       SL_ID                                              NUMBER(2)
       HEADING                                            VARCHAR2(3120)
       DESCRIPTION                                        VARCHAR2(3900)
      
      SQL> desc news_location
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                            NUMBER(14)
       COUNTRY                                            VARCHAR2(32)
       REGION                                             NUMBER(2)
      
      SQL> desc news_product
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                            NUMBER(14)
       PRODUCT_CATEGORY_ID                       NOT NULL NUMBER(14)
       PRODUCT                                            VARCHAR2(27)
      
      SQL> desc news_service
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                            NUMBER(14)
       SERVICE_ID                                NOT NULL NUMBER(14)
       SRVIS                                              VARCHAR2(16)
      
      SQL> desc news_info
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       NEWS_ID                                            NUMBER(14)
       SOURCE                                             VARCHAR2(203)
       ORIGIONAL_NEWS                                     VARCHAR2(3900)
       HEADING                                            VARCHAR2(3110)
      
      SQL> select count(*) from news where TO_CHAR(news_date,'YYYY') <  2012;
      
        COUNT(*)
      ----------
            8759
      I am trying to delete news that are published before 2012 but detail tables consists child record on basis of news_id. Certainly simple delete query will not work

      Please advise and thanks in anticipation
        • 1. Re: Deleting record on basis of master table ID
          Karthick_Arp
          If NEWS.NEWS_ID is a primary key and all the child table refers to it and if you have ON DELETE CASCADE on the NEWS.NEWS_ID enabled, you don't have to worry. You can just delete the records in NEWS table and the child records will be automatically deleted.

          If the above case is not true for you then you need to perform delete on child table one by one and then delete parent table.
          • 2. Re: Deleting record on basis of master table ID
            Frank Kulash
            Hi,
            Christy H. wrote:
            ... I am trying to delete news that are published before 2012 but detail tables consists child record on basis of news_id. Certainly simple delete query will not work
            DELETE will work if there are no foreign key constraints referencing the row to be deleted.
            If you do have foreign key constraints, you can change them to "ON DELETE CASCADE". That way, if you DELETE a row in a parent table, all child rows will auotmatically be DELETEd, too.
            There's also "ON DELETE SET NULL", which allows you to DELETE parent rows that have children. When you DELETE a parent, child rows are not automatically DELETEd, but the column(s) that pointed to the DELETEd parent are automatically changed to NULL.
            • 3. Re: Deleting record on basis of master table ID
              jeneesh
              ON DELETE CASCADE?
              • 4. Re: Deleting record on basis of master table ID
                Nitesh.
                My suggestion is check the constraints on all dependant tables and give whole information about constraints also so that we can suggest the best or else one more suggestion is if number of child tables are not more means invalidate the constraints and just delete the master table data's so in this way if u want u can even preserve your child table data's or else set on delete cascade by altering the constraints and give delete on master table ..
                • 5. Re: Deleting record on basis of master table ID
                  Christy H.
                  Hello,

                  Thanks for replies

                  When I created the tables I not described any ON DELETE CASCADE to parent or child table. Can I altered and then execute this query?
                  SQL> delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE;
                  delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE
                                                                           *
                  ERROR at line 1:
                  ORA-00933: SQL command not properly ended
                  Thanks again
                  • 6. Re: Deleting record on basis of master table ID
                    Bawer
                    ON DELETE CASCADE should be defined at foreign key definition. you can't use this keyword in DML.

                    an another way:
                    first, delete rows from child table, and than delete from main table:
                    delete from news_location where news_id in (
                      select news_id from news where TO_CHAR(news_date,'YYYY') <  2008
                    );
                    
                    delete from child2 ...
                    
                    delete from news where TO_CHAR(news_date,'YYYY') <  2008
                    • 7. Re: Deleting record on basis of master table ID
                      Nitesh.
                      Yes you do alteration of constraints and try .. It will surely work out...
                      • 8. Re: Deleting record on basis of master table ID
                        Purvesh K
                        Christy H. wrote:
                        Hello,

                        Thanks for replies

                        When I created the tables I not described any ON DELETE CASCADE to parent or child table. Can I altered and then execute this query?
                        SQL> delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE;
                        delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE
                        *
                        ERROR at line 1:
                        ORA-00933: SQL command not properly ended
                        Thanks again
                        ON DELETE CASCADE is a clause to be specified in the Foreign Key constraint, than to be specified in the DML query.

                        To achieve the task easily, you will have to Drop the Existing Foreign Key constraints (if any) and then re-create those with ON DELETE Cascade clause.

                        See below:
                        create table test_parent(pk_col number primary key, col varchar2(1));
                        create table test_child(pk_col_child number primary key, fk_col number, col1 varchar2(1));
                        
                        alter table test_child add constraint fk_pk_col foreign key (fk_col) references test_parent(pk_col);
                        
                        select constraint_name, constraint_type, table_name, delete_rule
                          from user_constraints
                         where constraint_name= 'FK_PK_COL';
                        
                        CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     DELETE_RULE 
                        ------------------------------ --------------- ------------------------------ ----------- 
                        FK_PK_COL                      R               TEST_CHILD                     NO ACTION 
                        
                        alter table test_child drop constraint fk_pk_col;
                        
                        alter table test_child add constraint fk_pk_col foreign key (fk_col) references test_parent(pk_col) on delete cascade;
                        
                        select constraint_name, constraint_type, table_name, delete_rule
                          from user_constraints
                         where constraint_name= 'FK_PK_COL';
                        
                        CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME                     DELETE_RULE 
                        ------------------------------ --------------- ------------------------------ ----------- 
                        FK_PK_COL                      R               TEST_CHILD                     CASCADE
                        After this, any Delete on Parent will be cascaded to Child tables.
                        • 9. Re: Deleting record on basis of master table ID
                          jeneesh
                          Niteshkhush wrote:
                          Yes you do alteration of constraints and try .. It will surely work out...
                          I dont think you can ALTER a constriant to make it ON DELETE CASCADE....

                          Drop and re-create will be an option..

                          But it is dangerous...

                          Read at AskTom
                          • 10. Re: Deleting record on basis of master table ID
                            Karthick_Arp
                            A simple example..
                            SQL> create table parent
                              2  (
                              3    id integer primary key
                              4  );
                             
                            Table created.
                             
                            SQL> create table child
                              2  (
                              3    id_child integer, constraint child_fk foreign key (id_child) references parent (id)
                              4  );
                             
                            Table created.
                             
                            SQL> insert into parent values (1);
                             
                            1 row created.
                             
                            SQL> insert into child values (1);
                             
                            1 row created.
                             
                            SQL> commit;
                             
                            Commit complete.
                             
                            SQL> select * from parent;
                             
                                    ID
                            ----------
                                     1
                             
                            SQL> select * from child;
                             
                              ID_CHILD
                            ----------
                                     1
                             
                            SQL> delete from parent;
                            delete from parent
                            *
                            ERROR at line 1:
                            ORA-02292: integrity constraint (ARBORU.CHILD_FK) violated - child record found
                             
                            
                            SQL> alter table child drop constraint child_fk;
                             
                            Table altered.
                             
                            SQL> alter table child add constraint child_fk foreign key (id_child) references parent (id) on delete cascade;
                             
                            Table altered.
                             
                            SQL> delete from parent;
                             
                            1 row deleted.
                             
                            SQL> select * from parent;
                             
                            no rows selected
                             
                            SQL> select * from child;
                             
                            no rows selected
                             
                            SQL> 
                            • 11. Re: Deleting record on basis of master table ID
                              Christy H.
                              Thanks again for favorable replies
                              SQL> alter table news_info drop constraint NEWS_INFO_FK;
                              
                              Table altered.
                              
                              SQL> alter table news_info add constraint NEWS_INFO_FK FOREIGN KEY (news_id) REF
                              ERENCES   news(news_id) on delete cascade enable novalidate ;
                              
                              Table altered.
                              
                              SQL> alter table news_info modify constraint NEWS_INFO_FK validate ;
                              
                              Table altered.
                              
                              
                              SQL> alter table news_location drop constraint NEWS_LOCATION_FK;
                              
                              Table altered.
                              
                              SQL> alter table news_location add constraint NEWS_LOCATION_FK FOREIGN KEY (news
                              _id) REFERENCES   news(news_id) on delete cascade enable novalidate ;
                              
                              Table altered.
                              
                              SQL> alter table news_location modify constraint NEWS_LOCATION_FK validate ;
                              
                              Table altered.
                              
                              SQL> alter table news_PRODUCT drop constraint NEWS_PRODUCT_FK;
                              
                              Table altered.
                              
                              SQL> alter table news_PRODUCT add constraint NEWS_PRODUCT_FK FOREIGN KEY (news_i
                              d) REFERENCES   news(news_id) on delete cascade enable novalidate ;
                              
                              Table altered.
                              
                              SQL> alter table news_PRODUCT modify constraint NEWS_PRODUCT_FK validate ;
                              
                              Table altered.
                              
                              SQL> alter table news_SERVICE drop constraint NEWS_SERVICE_FK;
                              
                              Table altered.
                              
                              SQL> alter table news_SERVICE add constraint NEWS_SERVICE_FK FOREIGN KEY (news_i
                              d) REFERENCES   news(news_id) on delete cascade enable novalidate ;
                              
                              Table altered.
                              
                              SQL> alter table news_SERVICE modify constraint NEWS_SERVICE_FK validate ;
                              
                              Table altered.
                              
                              SQL> delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE;
                              
                              delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE
                                                                                       *
                              ERROR at line 1:
                              ORA-00933: SQL command not properly ended
                              
                              
                              SQL> select constraint_name, constraint_type, table_name, delete_rule
                                2    from user_constraints
                                3   where constraint_name= 'NEWS_SERVICE_FK';
                              
                              CONSTRAINT_NAME                C TABLE_NAME                     DELETE_RU
                              ------------------------------ - ------------------------------ ---------
                              NEWS_SERVICE_FK                R NEWS_SERVICE                   CASCADE
                              
                              SQL> select constraint_name, constraint_type, table_name, delete_rule
                                2    from user_constraints
                                3   where constraint_name= 'NEWS_PRODUCT_FK';
                              
                              CONSTRAINT_NAME                C TABLE_NAME                     DELETE_RU
                              ------------------------------ - ------------------------------ ---------
                              NEWS_PRODUCT_FK                R NEWS_PRODUCT                   CASCADE
                              
                              SQL> select constraint_name, constraint_type, table_name, delete_rule
                                2    from user_constraints
                                3   where constraint_name= 'NEWS_LOCATION_FK';
                              
                              CONSTRAINT_NAME                C TABLE_NAME                     DELETE_RU
                              ------------------------------ - ------------------------------ ---------
                              NEWS_LOCATION_FK               R NEWS_LOCATION                  CASCADE
                              
                              SQL> select constraint_name, constraint_type, table_name, delete_rule
                                2    from user_constraints
                                3   where constraint_name= 'NEWS_INFO_FK';
                              
                              CONSTRAINT_NAME                C TABLE_NAME                     DELETE_RU
                              ------------------------------ - ------------------------------ ---------
                              NEWS_INFO_FK                   R NEWS_INFO                      CASCADE
                              
                              SQL> delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE;
                              
                              delete from news where TO_CHAR(news_date,'YYYY') <  2008 ON DELETE CASCADE
                                                                                       *
                              ERROR at line 1:
                              ORA-00933: SQL command not properly ended
                              
                              
                              SQL>
                              Edited by: Christy H. on Jan 17, 2013 1:58 AM

                              I removed ON DELETE CASCADE and ran again
                              delete from news where TO_CHAR(news_date,'YYYY') <  2008; 
                              Wow deleted!!

                              This forum is miracle to learn oracle. Thanks from the bottom of my heart
                              • 12. Re: Deleting record on basis of master table ID
                                jeneesh
                                Just do as below.. You dont need to mention ON DELETE CASCADE in the delete statement.. You have to define it in the constraint and you already did it..
                                So, now when you delete parent, curresponding children will get automatically deleted..
                                delete from news where TO_CHAR(news_date,'YYYY') <  2008 ;
                                • 13. Re: Deleting record on basis of master table ID
                                  Purvesh K
                                  Again, I tell you, do not need ON DELETE CASCADE in the Delete query.

                                  Just use the Delete query as:-
                                  delete from news where TO_CHAR(news_date,'YYYY') <  2008;
                                  And the Child tables shall be deleted accordingly.
                                  • 14. Re: Deleting record on basis of master table ID
                                    Nitesh.
                                    Great ... Finally got your solution ..
                                    1 2 Previous Next