How to truncate reference partitioning table in oracle database

How to truncate reference partitioning table in oracle database

there is no option to truncate reference partitioning tables in oracle database unless FK tables delete rule is set CASCADE. it is tested in 12.2

how do we get rid of PK table while keeping FK table exists , It depends on the way the tables are got created.

Possible ways:

1. use delete
2. drop FK tables, truncate PK tables since FK delete rule is 'NO ACTION'
3. truncate parent table would be successful only when FK tables delete rule is set to CASCADE.
4. TRUNCATE table partition (required partitions ) with cascade option.

Here it is the demo when FK tables delete rule is NO ACTION

SQL> create table p_emp(
   empno      number  primary key,
   job        varchar2(20),
   sal        number(7,2),
   deptno     number(2)
   )
   partition by list(job)
   ( partition p_job_dba values ('BA'),
     partition p_job_mgr values ('GR'),
     partition p_job_vp  values ('VP')
   );

SQL>  
Table created.

 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno foreign key(empno)
    references p_emp(empno)
 )
 partition by reference (fk_empno)
 /


SQL>  

Table created.

SQL> truncate table p_emp cascade;
truncate table p_emp cascade
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL> delete from p_emp;

0 rows deleted.

SQL> truncate table r_emp;

Table truncated.

SQL>  truncate table p_emp cascade;
 truncate table p_emp cascade
                *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


SQL>


Here it is the demo when FK tables delete rule is CASCADE





 SQL> create table p_emp(
    empno      number  primary key,
    job        varchar2(20),
    sal        number(7,2),
    deptno     number(2)
    )
    partition by list(job)
    ( partition p_job_dba values ('BA'),
      partition p_job_mgr values ('GR'),
      partition p_job_vp  values ('VP')
    );

Table created.



SQL>
 create table r_emp
 (
 ename      varchar2(10),
 emp_id     number  primary key,
 empno      not null,
 constraint fk_empno1 foreign key(empno)
    references p_emp(empno) on delete cascade
 )
 partition by reference (fk_empno1)
 /

SQL>   
Table created.

SQL> SQL>
SQL>  truncate table p_emp cascade;

Table truncated.

SQL>


 

How to check FK delete rule:

the below query help us to know the delete rule of FK tables FK constraints.

when delete rule is not set :

select table_name, delete_rule
from user_constraints
where table_name like 'TABLE_NAME_YOURS%'
and constraint_type = 'R';

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR        NO ACTION
TABLE_NAME_YOURS_LOG         NO ACTION
TABLE_NAME_YOURS_LOG_P       NO ACTION


when delete rule is set

TABLE_NAME                     DELETE_RULE
------------------------------ ---------
TABLE_NAME_YOURS_CHAR          CASCADE
TABLE_NAME_YOURS_LOG           CASCADE
TABLE_NAME_YOURS_LOG_P         CASCADE

4.
SQL> ALTER TABLE xxxx TRUNCATE PARTITION P2019xxx      UPDATE INDEXES;

Table truncated.

SQL>