TRUNCATE CASCADE Oracle Database 12C release 1 (12.1)

In Oracle 12c new word CASCADE can be specified for TRUNCATE command.

TRUNCATE <table_name> CASCADE

If you specify CASCADE, then Oracle Database truncates all child tables that reference TABLE with an enabled ON DELETE referential constraint. This is a recursive operation that will truncate all child tables, grandchild tables, and so on, using the specified options.

create table master_tbl
(
  id1 number primary key,
  id2 number
);

create table child_tbl
(
  id1 number primary key,
  id2 number
);

create table grandchild_tbl
(
  id1 number primary key,
  id2 number
);

alter table child_tbl 
add constraint child_tbl_fk foreign key (id2) 
references master_tbl ON DELETE CASCADE;

alter table grandchild_tbl 
add constraint grandchild_tbl_fk foreign key (id2) 
references child_tbl ON DELETE CASCADE;

add data

insert into master_tbl values(1,1);

insert into child_tbl values(1,1);

insert into grandchild_tbl values(1,1);
commit;

select * from master_tbl;

       ID1        ID2
---------- ----------
         1          1

select * from child_tbl;

       ID1        ID2
---------- ----------
         1          1

select * from grandchild_tbl;

       ID1        ID2
---------- ----------
         1          1

simple truncate won’t work and raises exception ORA-02266

truncate table master_tbl;

SQL Error: ORA-02266: unique/primary keys 
in table referenced by enabled foreign key

extended TRUNCATE .. CASCADE works and truncates master and child, grandchild tables

truncate table master_tbl CASCADE;

select count(*) from master_tbl;

  COUNT(*)
----------
         0

select count(*) from child_tbl;

  COUNT(*)
----------
         0

select count(*) from grandchild_tbl;

  COUNT(*)
----------
         0

This option works as well for partitioned tables but only on table level. If you want to use this option on partition or subpartition level it works only for reference partitioning.

create table master_range_tbl
(id number primary key)
partition by range (id)
(
  partition p1 values less than(10),
  partition p2 values less than(20)
);

create table child_range_tbl
(id number primary key)
partition by range (id)
(
  partition p1 values less than(10),
  partition p2 values less than(20)
);

alter table child_range_tbl
add constraint child_range_tbl_fk foreign key (id) 
references master_range_tbl ON DELETE CASCADE;

begin
  for i in 1..15
  loop
    insert into master_range_tbl values(i);
    insert into child_range_tbl values(i);
  end loop;

  commit;
end;
/

select 'MASTER_RANGE_TBL' table_name,count(*) cnt 
from master_range_tbl
union all
select 'CHILD_RANGE_TBL', count(*) 
from child_range_tbl;

TABLE_NAME              CNT
---------------- ----------
MASTER_RANGE_TBL         15 
CHILD_RANGE_TBL          15

truncate table master_range_tbl cascade;

select 'MASTER_RANGE_TBL' table_name,count(*) cnt 
from master_range_tbl
union all
select 'CHILD_RANGE_TBL', count(*) from child_range_tbl;

TABLE_NAME              CNT
---------------- ----------
MASTER_RANGE_TBL          0 
CHILD_RANGE_TBL           0

Have a fun 🙂

Tomasz

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *