Skip to Main Content
  • Questions

  • Truncate Partitions on Referenced-Partitioned Table

Breadcrumb

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.