Skip to Main Content
  • Questions
  • Truncate Partitions on Referenced-Partitioned Table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Nikhil.

Asked: January 19, 2020 - 3:28 pm UTC

Last updated: August 30, 2022 - 1:26 am UTC

Version: 12.1.0.1

Viewed 10K+ times! This question is

You Asked

Hi,
Can you please have a look at below scenario
Table Name: EMP Primary Key EMPID
Columns:
EMPID NUMBER,
NAME VARCHAR2(10)
RECORD_DATE DATE

Table Name: EMP_HIST Foreign Key: EMP.EMPID

EMP & EMP_HIST are referenced-partitioned on column: RECORD_DATE

Now I am trying to truncate one of partition on EMP table but getting
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys"

alter table EMP truncate partition PYTR_P_MAXVALUE;

I tried truncate child table EMP_HIST using:
alter table EMP_HIST truncate partition PYTR_P_MAXVALUE; -- its a success

but still I am not able to alter table EMP truncate partition PYTR_P_MAXVALUE;

Could you please help here.

and Connor said...

The foreign key must be ON DELETE CASCADE

Without it you get this:

SQL> create table CHILD
  2    ( c int primary key,
  3      p int references PARENT ( p )
  4           );

Table created.

SQL> insert into PARENT values (1);
SQL> insert into CHILD  values (1,1);

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



and with it you get this

SQL> truncate table PARENT cascade;

Table truncated.







Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

even with on delete cascade - still error.

Rajeshwaran, Jeyabal, August 05, 2022 - 3:08 pm UTC

Team,

can you help us to understand why we got error while Truncate T1 with CASCADE option - with all FK's are bounded with "on delete cascade" option.

but truncate on T2 as working fine. let us know what are we missing here. we got a similar scenario in our application.

demo@ATP19C> create table t1(x number primary key)
  2  partition by hash(x)
  3  ( partition p1 );

Table created.

demo@ATP19C>
demo@ATP19C> create table t2(x number primary key, y number not null,
  2     constraint t2_fk foreign key(y)
  3     references t1
  4     on delete cascade)
  5  partition by reference(t2_fk);

Table created.

demo@ATP19C> create table t3(x number primary key, y number not null, z number not null,
  2     constraint t3_fk1 foreign key(y)
  3     references t2
  4     on delete cascade ,
  5     constraint t3_fk2 foreign key(z)
  6     references t1
  7     on delete cascade )
  8  partition by reference(t3_fk1);

Table created.

demo@ATP19C> insert into t1 values(1);

1 row created.

demo@ATP19C> insert into t2 values(1,1);

1 row created.

demo@ATP19C> insert into t3 values(1,1,1);

1 row created.

demo@ATP19C> commit;

Commit complete.

demo@ATP19C> alter table t1 truncate partition p1 CASCADE;
alter table t1 truncate partition p1 CASCADE
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


demo@ATP19C> alter table t2 truncate partition p1 CASCADE;

Table truncated.

demo@ATP19C>

Connor McDonald
August 08, 2022 - 1:44 am UTC

Its because of the "circular" reference, ie, you have a two paths from t3 back to t1. Because of this we stop the truncate because there is now a non-deterministic path through the tables.

eg

SQL> create table t1(x number primary key)
  2      partition by hash(x)
  3      ( partition p1 );

Table created.

SQL>
SQL> create table t2(x number primary key, y number not null,
  2         constraint t2_fk foreign key(y)
  3         references t1
  4         on delete cascade)
  5      partition by reference(t2_fk);

Table created.

SQL>
SQL> create table t3(x number primary key, y number not null, z number not null,
  2         constraint t3_fk1 foreign key(y)
  3         references t2
  4         on delete cascade
  5         --,
  6         --constraint t3_fk2 foreign key(z)
  7         --references t1
  8         --on delete cascade
  9         )
 10      partition by reference(t3_fk1);

Table created.

SQL>
SQL> insert into t1 values(1);

1 row created.

SQL> insert into t2 values(1,1);

1 row created.

SQL> insert into t3 values(1,1,1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table t1 truncate partition p1 CASCADE;

Table truncated.

SQL> alter table t2 truncate partition p1 CASCADE;

Table truncated.

SQL>
SQL>




Reference partition table having on delete cascade option

Rajeshwaran, Jeyabal, August 25, 2022 - 5:26 am UTC

Team,

the below testcase was run from Oracle 21c(21.3)

got a child table with FK defined as "on delete cascade" option.

but still the Truncate partition CASCADE fails, while the table level Truncate CASCADE command works. why?

demo@PDB1> create table t1(x int, y date,
  2     constraint t1_pk
  3     primary key(x) )
  4  partition by range( y )
  5  ( partition p2021 values less than (
  6             to_date('01-Jan-2022','dd-mon-yyyy') ) ,
  7    partition p2022 values less than (
  8             to_date('01-Jan-2023','dd-mon-yyyy') )  );

Table created.

demo@PDB1> insert into t1(x,y) values(1, to_date('17-feb-2021','dd-mon-yyyy') );

1 row created.

demo@PDB1> insert into t1(x,y) values(2, to_date('17-feb-2022','dd-mon-yyyy') );

1 row created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> create table t2(x int primary key, y int,
  2     constraint t2_fk foreign key(y)
  3     references t1
  4     on delete cascade);

Table created.

demo@PDB1> insert into t2(x,y) values(1,1);

1 row created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> alter table t1 truncate partition p2021 ;
alter table t1 truncate partition p2021
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


demo@PDB1> alter table t1 truncate partition p2021 CASCADE;
alter table t1 truncate partition p2021 CASCADE
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


demo@PDB1> truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


demo@PDB1> truncate table t1 CASCADE;

Table truncated.

demo@PDB1>

Connor McDonald
August 26, 2022 - 5:42 am UTC

It is not supported.

You can "truncate cascade" table to table, or you can "truncate partition cascade" in a *reference* partitioning scheme.

Reference partition table having on delete cascade option

Rajeshwaran, Jeyabal, August 26, 2022 - 9:45 am UTC

sorry - getting confused.

when you say this "...It is not supported...." - can understand that "truncate partition cascade" is not supported.

but when you say this "...or you can "truncate partition cascade" in a *reference* partitioning scheme...." -

then why the above demo on a reference partition table doesn't support truncate partition cascade option ?

Reference partition table having on delete cascade option

Rajeshwaran, Jeyabal, August 26, 2022 - 11:12 am UTC

Team,

sorry for the misunderstanding.

once i got T2 reference partitioned the truncate partition cascade works correct.

sorry i forgot to mention the reference partition at my demo. defined the FK but forgot to mention reference partition part.

demo@PDB1> create table t2(x int primary key, y int not null,
  2         constraint t2_fk foreign key(y)
  3         references t1
  4         on delete cascade)
  5  partition by reference(t2_fk);

Table created.

demo@PDB1> insert into t2(x,y) values(1,1);

1 row created.

demo@PDB1> commit;

Commit complete.

demo@PDB1> alter table t1 truncate partition p2021 ;
alter table t1 truncate partition p2021
            *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


demo@PDB1> alter table t1 truncate partition p2021 CASCADE;

Table truncated.

demo@PDB1>

Connor McDonald
August 30, 2022 - 1:26 am UTC

all good

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.