This discussion is archived
3 Replies Latest reply: Jul 31, 2013 11:06 PM by Jolvin RSS

Foreign-key autocreation in child table giving issue in the application

Jolvin Newbie
Currently Being Moderated

Hi,

  I am facing an issue with partitioning a table that foreign key relationship is created on the original table with interim table. This gives exception in the application because of its existence. Please suggest me how to get rid of this issue.

 

Let's say my table T_TABLENAME has to be partitioned. It has a child table T_CHILD_TABLENAME which references (FK_T1) ID column of T_TABLENAME.

While partitioning, COPY_TABLE_DEPENDENTS function copies/creates the key/index/trigger objects for the interim table. [I need copy_constraints => TRUE in COPY_TABLE_DEP call], Fine.

But, after partitioning is done, the foreign key (TMP$$_FK_T1) object exists with the child table which should absolutely not happen as this forms dependency with interim table as well along with parent table.

 

Here my script goes:

 

i) Creating interim table

CREATE TABLE T_TABLENAME_PT

PARTITION BY RANGE (CREATED_DATE)

(PARTITION P_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy')),

PARTITION P_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy')),

PARTITION P_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','dd-MON-yyyy')),

PARTITION P_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy')),

PARTITION P_RECENT VALUES LESS THAN (MAXVALUE))

AS SELECT * FROM T_TABLENAME WHERE 1=2;

 

ii) Partitioning Script

declare

  v_username varchar2(50);

  v_exception varchar2(220);

  l_num_errors PLS_INTEGER;

  v_source_table  varchar2(35) := 'T_TABLENAME';

  v_interim_table varchar2(35) := 'T_TABLENAME_PT';

BEGIN

 

  select USERNAME into v_username from USER_USERS where rownum <= 1;

   

  begin

 

      DBMS_REDEFINITION.CAN_REDEF_TABLE(v_username, v_source_table, DBMS_REDEFINITION.CONS_USE_PK);

 

      DBMS_REDEFINITION.START_REDEF_TABLE(

        uname      => v_username,

        orig_table => v_source_table,

        int_table  => v_interim_table);

 

      DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

        uname      => v_username,

        orig_table => v_source_table,

        int_table  => v_interim_table,

        copy_indexes => 1,

        copy_triggers => TRUE,

        copy_constraints => TRUE,

        copy_privileges => TRUE,

        ignore_errors => TRUE,

        num_errors => l_num_errors);

    

      DBMS_REDEFINITION.SYNC_INTERIM_TABLE(v_username, v_source_table, v_interim_table);

 

      begin

       DBMS_REDEFINITION.FINISH_REDEF_TABLE(

        UNAME      => v_username,

        ORIG_TABLE => v_source_table,

        INT_TABLE  => v_interim_table);       

 

     EXCEPTION

     WHEN OTHERS THEN

       DBMS_REDEFINITION.ABORT_REDEF_TABLE(

        UNAME      => v_username,

        ORIG_TABLE => v_source_table,

        INT_TABLE  => v_interim_table);

 

      end;     

       

      exception

          when others then

            v_exception :=substr(SQLERRM,1,150);

  end;

 

exception

  when others then

    v_exception := substr(SQLERRM,1,175);

END;

  • 1. Re: Foreign-key autocreation in child table giving issue in the application
    clcarter Expert
    Currently Being Moderated

    This might be helpful, quote, "Reference ... Allows tables with a parent-child relationship to be logically equi-partitioned by inheriting the partition key from the parent table without duplicating the key columns"

     

    Manipulating partitions in Oracle Database 11g

     

    Its kind of important (well, REALLY important) to keep constraints intact. They are in the schema for a reason, the main one being data integrity, its not good to disregard relationship definitions. Can lead to all kinds of data problems for an application. Not so much of a "problem" for the database, as the instance does what it is told to do as long as DML or DDL obeys the rules of the engine. If you turn off a "rule" of a parent-child relationship the heath of the application will probably suffer.

     

    And the partitioning option is specifically mentioned in the XE license information as not included, so this probably is not the optimal forum, not seeing a specific "partitioning" forum either. *Maybe* a post to General Questions might have better and quicker answers on partitioning.

  • 2. Re: Foreign-key autocreation in child table giving issue in the application
    rp0428 Guru
    Currently Being Moderated

    This question belongs in the SQL and PL/SQL forum. Please mark it ANSWERED and repost it in the proper forum

    https://forums.oracle.com/community/developer/english/oracle_database/sql_and_pl_sql

     

    Whenever you post provide your 4 digit Oracle version.

     

    You also need to post ALL table and index DDL and some sample data so people can try to reproduce your problem.

     

    The code you posted also has two MAJOR flaws:

     

    1. It uses three WHEN OTHERS exception handlers that HIDE any exception that occurs. One of the handlers does the abort but none of them report on the exception that occurred or re-raise it.

     

    2. It does not check the result of the 'CAN_REDEF_TABLE' execution. It is MANDATORY to check that the redefinition is actually allowed before proceeding to do it.

  • 3. Re: Foreign-key autocreation in child table giving issue in the application
    Jolvin Newbie
    Currently Being Moderated

    Thanks for your information. I am using Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - 64bit.

     

    1) I am logging the exceptions in a logger table.

    2) If CAN_REDEF_TABLE gives exception, flow goes to the final EXCEPTION block and program terminates. So if no exceptions, the other steps comes into the flow of execution.

     

    Complete Details

     

    (1) Parent Table (existing): T_SOH_SUBREQ_INSTALLATION_ADDR

     

    create table T_SOH_SUBREQ_INSTALLATION_ADDR

    (

      ACCOUNT_ADDRESS_ID  NUMBER(10),

      SUBREQ_ADDRESS_ID   NUMBER(10) not null,

      COMMUNITY_ID        NUMBER(10),

      STREET_ID           NUMBER(10),

      BUILDING_ID         NUMBER(10),

      CREATED_USER_ID     VARCHAR2(40) not null,

      MODIFIED_USER_ID    VARCHAR2(40) not null,

      CREATED_DATE        TIMESTAMP(6) not null,

      MODIFIED_DATE       TIMESTAMP(6) not null,

      DELETION_STATUS     CHAR(1) not null

    );

     

    alter table T_SOH_SUBREQ_INSTALLATION_ADDR

      add constraint PK_T_SOH_SUBREQ_INST_ADDR primary key (SUBREQ_ADDRESS_ID);

     

    alter table T_SOH_SUBREQ_INSTALLATION_ADDR

      add constraint FK_T_SOH_SUBREQ_INSTALLATIO624 foreign key (ACCOUNT_ADDRESS_ID)

      references T_SOH_ACCT_INSTALLATION_ADDR (ACCOUNT_ADDRESS_ID);

     

    (2) Child Table (existing): T_SOH_SUBREQ_LINKED_INST_ADDR

     

    create table T_SOH_SUBREQ_LINKED_INST_ADDR

    (

      CREATED_DATE      TIMESTAMP(6) not null,

      CREATED_USER_ID   VARCHAR2(40) not null,

      MODIFIED_DATE     TIMESTAMP(6) not null,

      MODIFIED_USER_ID  VARCHAR2(20) not null,

      DELETION_STATUS   CHAR(1) not null,

      SUBREQ_ADDRESS_ID NUMBER(10) not null,

      SUBREQUEST_ID     NUMBER(10) not null,

      CIRCUIT_POINT     NUMBER(10)

    );

     

    alter table T_SOH_SUBREQ_LINKED_INST_ADDR

      add constraint PK_T_SOH_SUBREQ_LINK_INST_ADDR primary key (SUBREQ_ADDRESS_ID, SUBREQUEST_ID);

     

    alter table T_SOH_SUBREQ_LINKED_INST_ADDR

      add constraint FK_T_SOH_SUBREQ_LINKED_INST626 foreign key (SUBREQ_ADDRESS_ID)

      references T_SOH_SUBREQ_INSTALLATION_ADDR (SUBREQ_ADDRESS_ID);

     

    (3) Partitioning is done on Parent Table


    CREATE TABLE T_TMP_PARTITION_LOGS

    (

      LOG_MSG  VARCHAR2(250),

      LOG_TIME TIMESTAMP(6)

    );


    CREATE TABLE T_SOH_SUBREQ_INSTALL_ADDR_PT

    PARTITION BY RANGE (CREATED_DATE)

    (PARTITION P_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy')),

    PARTITION P_2009 VALUES LESS THAN (TO_DATE('01-JAN-2010','dd-MON-yyyy')),

    PARTITION P_2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','dd-MON-yyyy')),

    PARTITION P_2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','dd-MON-yyyy')),

    PARTITION P_RECENT VALUES LESS THAN (MAXVALUE))

    AS SELECT * FROM T_SOH_SUBREQ_INSTALLATION_ADDR WHERE 1=2;

     

    insert into t_tmp_partition_logs(log_msg,log_time) values('01_CreateTable: T_SOH_SUBREQ_INSTALL_ADDR_PT Table Created', systimestamp);

     

    (4) Script for REDEFINITION

     

    declare

      v_username varchar2(50);

      v_exception varchar2(220);

      l_num_errors PLS_INTEGER;

      v_source_table  varchar2(35) := 'T_SOH_SUBREQ_INSTALLATION_ADDR';

      v_interim_table varchar2(35) := 'T_SOH_SUBREQ_INSTALL_ADDR_PT';

      v_file_name     varchar2(20) := '02_Redefine';

    BEGIN

     

      insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name || '.sql --> Starts', systimestamp);

     

      select USERNAME into v_username from USER_USERS where rownum <= 1;

     

      insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name || ': UserName-'|| v_username, systimestamp);

       

      begin

     

          DBMS_REDEFINITION.CAN_REDEF_TABLE(v_username, v_source_table, DBMS_REDEFINITION.CONS_USE_PK);

        

          insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': '|| v_source_table ||' After CAN_REDEF_TABLE', systimestamp);

     

          DBMS_REDEFINITION.START_REDEF_TABLE(

            uname      => v_username,

            orig_table => v_source_table,

            int_table  => v_interim_table);

           

          insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': '|| v_source_table ||' After START_REDEF_TABLE', systimestamp);

     

          DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(

            uname      => v_username,

            orig_table => v_source_table,

            int_table  => v_interim_table,

            copy_indexes => 1,

            copy_triggers => TRUE,

            copy_constraints => TRUE,

            copy_privileges => TRUE,

            ignore_errors => TRUE,

            num_errors => l_num_errors);

        

          insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': '|| v_source_table ||' After COPY_TABLE_DEPENDENTS - l_num_errors:' || l_num_errors, systimestamp);

        

          DBMS_REDEFINITION.SYNC_INTERIM_TABLE(v_username, v_source_table, v_interim_table);

     

          insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': After SYNC_INTERIM_TABLE on '|| v_source_table, systimestamp);

     

          begin

           DBMS_REDEFINITION.FINISH_REDEF_TABLE(

            UNAME      => v_username,

            ORIG_TABLE => v_source_table,

            INT_TABLE  => v_interim_table);       

          insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': After FINISH_REDEF_TABLE on '|| v_source_table, systimestamp);

         EXCEPTION

         WHEN OTHERS THEN

           DBMS_REDEFINITION.ABORT_REDEF_TABLE(

            UNAME      => v_username,

            ORIG_TABLE => v_source_table,

            INT_TABLE  => v_interim_table);

           insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': Aborted '|| v_source_table, systimestamp);

          end;     

        insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||': '|| v_source_table ||' redefined', systimestamp);

           

          exception

              when others then

                v_exception :=substr(SQLERRM,1,150);

                insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||'-EXCEPTION:'|| v_source_table ||'-' || v_exception, systimestamp);

      end;

     

      insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||'.sql <-- Ends', systimestamp);

     

    exception

      when others then

        v_exception := substr(SQLERRM,1,175);

        insert into t_tmp_partition_logs(log_msg,log_time) values(v_file_name ||'-EXCEPTION:' || v_exception, systimestamp);

    END;

    /

     

    ==> NOW THE ISSUE


    My child table T_SOH_SUBREQ_LINKED_INST_ADDR is having another foreign key column TMP$$_FK_T_SOH_SUBREQ_LIN4 with the interim table T_SOH_SUBREQ_INSTALL_ADDR_PT.

Legend

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