This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Jan 17, 2013 2:04 AM by jeneesh RSS

Deleting record on basis of master table ID

Christy H. Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    ON DELETE CASCADE?
  • 4. Re: Deleting record on basis of master table ID
    Nitesh. Explorer
    Currently Being Moderated
    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. Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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. Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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. Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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. Explorer
    Currently Being Moderated
    Great ... Finally got your solution ..
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points