Oracle Forced dropping of Foreign Key during purge on Interval Partitioned Tables

Question

We have several tables which are interval partitioned by day. While working on a purge job to drop a day of partitioning, our DBA has informed us that we will have to drop all foreign keys to any table, before performing the purge. This seems like an unnecessary step, and thus, I am turning to the wisdom of stackoverflow.

 parentTable     childTable
 ID   (PK)       ID   (PK)(FK)
 date (PK)       date (PK)(FK)
                 otherKey(PK)

 parentTable             childTable
 ID     date         ID    date    otherKey  
 1       10/23        1     10/23     a
 2       10/23        2     10/23     a
 3       10/23        3     10/23     a
 1       10/24        1     10/24     a
 2       10/24        2     10/24     a
                      2     10/24     b

The question is, if we were to drop the 10/23 partition from childTable (first), then parentTable, would we have to drop/disable the Foreign Key constraint before the purge, and create it again afterwards? Is there a data situation where this would have to occur (maybe not as shown in my example above).

Answer 1

Seems that the DBA was right, test case scenario:

CREATE TABLE parent_tab (
  id NUMBER PRIMARY KEY,
  start_date DATE
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-01-2013', 'DD-MM-YYYY')) 
);

INSERT INTO parent_tab VALUES (1, DATE '2012-01-01');
INSERT INTO parent_tab VALUES (2, DATE '2013-01-02');
INSERT INTO parent_tab VALUES (3, DATE '2013-01-03');

CREATE TABLE child_tab (
   start_date DATE,
   parent_tab_id NUMBER REFERENCES parent_tab(id)
)
PARTITION BY RANGE (start_date)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
( 
   PARTITION pos_data_p2 VALUES LESS THAN (TO_DATE('01-01-2013', 'DD-MM-YYYY'))
);

INSERT INTO child_tab VALUES (DATE '2012-01-01', 1);
INSERT INTO child_tab VALUES (DATE '2013-01-02', 2);
INSERT INTO child_tab VALUES (DATE '2013-01-03', 3);

COMMIT;

SELECT table_name, partition_name FROM user_tab_partitions WHERE table_name IN ('PARENT_TAB', 'CHILD_TAB');

TABLE_NAME                     PARTITION_NAME               
------------------------------ ------------------------------
CHILD_TAB                      POS_DATA_P2                    
CHILD_TAB                      SYS_P69                        
CHILD_TAB                      SYS_P70                        
PARENT_TAB                     POS_DATA_P2                    
PARENT_TAB                     SYS_P67                        
PARENT_TAB                     SYS_P68                        

ALTER TABLE child_tab DROP PARTITION SYS_P69;

> table CHILD_TAB altered.

ALTER TABLE parent_tab DROP PARTITION SYS_P67;

ALTER TABLE parent_tab DROP PARTITION SYS_P67
Error report:
SQL Error: ORA-02266 -  "unique/primary keys in table referenced by enabled foreign keys"
*Cause:    An attempt was made to truncate a table with unique or
           primary keys referenced by foreign keys enabled in another table.
           Other operations not allowed are dropping/truncating a partition of a
           partitioned table or an ALTER TABLE EXCHANGE PARTITION.
*Action:   Before performing the above operations the table, disable the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Edit

As the author pointed out, disabling the constraint works:

SELECT table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name = 'CHILD_TAB';

TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE
------------------------------ ------------------------------ ---------------
CHILD_TAB                      SYS_C0033723                   R               

ALTER TABLE child_tab DISABLE CONSTRAINT SYS_C0033723;

ALTER TABLE parent_tab DROP PARTITION SYS_P67;

> table PARENT_TAB altered.

ALTER TABLE child_tab ENABLE CONSTRAINT SYS_C0033723;

Answer 2

Some day i will learn to manage there my code, So..

CREATE OR REPLACE PROCEDURE manage_constraints (i_status IN varchar2)

IS

   CURSOR ref_cons

   IS

      SELECT constraint_name, table_name, status

      FROM user_constraints

      WHERE constraint_type in ( 'R')  ; -- YOu can disable more constraints type 


   v_status   VARCHAR2 (10);

   v_sql      VARCHAR2 (300);

BEGIN

   FOR e_cons IN ref_cons

   LOOP

      v_sql   :=

            'ALTER TABLE '

         || e_cons.table_name

         || ' '

         || i_status

         || '  CONSTRAINT '

         || e_cons.constraint_name;


      --DBMS_OUTPUT.put_line (v_sql);

      EXECUTE IMMEDIATE v_sql;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      RAISE;

END;


--exec manage_constraints('DISABLE');

--exec manage_constraints('ENABLE');

There you can just DISABLE all you constraints and later ENABLE them.

select * from user_constraints

check constraint types... hope this helps.

Answer 3

Not a direct answer, but it sounds like what you really want here is reference partitioning, which would:

• cascade partition maintenance operations
• Allow more simple queries
• Provide metadata that the two tables' partitions are logically associated
• Possibly add a small overhead on the inserts into the child tables.

http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm#CACIHDII