Total Pageviews

July 17, 2021

7/17/2021 07:13:00 PM

Oracle introduced TRUNCATE Casade feature in Oracle 12c.

What is use of that ?

For example

SQL>create table table1 (

  id           number,

  desc  varchar2(50),

  constraint table1_pk primary key (id)


SQL>create table table2 (

  id             number,

  t1_id          number,

  desc           varchar2(50),

  constraint table2_pk primary key (id),

  constraint table2_t1_fk foreign key (t1_id) references table1 (id) on delete cascade



 SQL>create table table3 (

  id             number,

  t2_id          number,

  desc           varchar2(50),

  constraint table3_pk primary key (id),

  constraint table3_t2_fk foreign key (t2_id) references table2 (id) on delete cascade



  insert into table1 values (1, 'test1 ONE');
  insert into table2 values (1, 1, 'test2 ONE');
  insert into table2 values (2, null, 'test2 TWO');
  insert into table3 values (1, 1, 'test3 ONE');
  insert into table3 values (2, null, 'test3 TWO');


SQL>truncate table tab1e1 cascade;


The TRUNCATE TABLE ... CASCADE command succeeds and recursively truncates all the dependent tables.

if we issue the command

SQL>select (select count(*) from tab1e1) as t1_count,
       (select count(*) from table2) as t2_count,
       (select count(*) from table3) as t3_count
from   dual;


---------- ---------- ----------
 0            0         0


it will delete all rows on dependent tables .i.e table1,table2,table3

Related Posts Plugin for WordPress, Blogger...