Truncate cascade : New feature of 12c

Truncate cascade : New feature of 12c

There is new feature in Oracle 12c, “Truncate cascade”. 12c onwards cascade option is available with truncate command.
It means that if you are truncating parent table’s data, corresponding records in child table also truncated.

Let’s have small demo, how it’s works,

create table order_mst
(ord_id number primary key,
ord_date date,
cust_name varchar2(500)
);

create table order_dtl
(ordtl_id number primary key,
ord_id number REFERENCES order_mst (ord_id) on delete cascade,
itm_name varchar2(500),
itm_price number
);


NOTE: Hope, you have notice on delete cascade option added with foreign key. This one is the key point. If you are not mentioning it during table creation, you cannot use the cascade option with truncate command.

Let's few demo rows in both parent and child tables as below,

insert into order_mst values (1,sysdate, 'Scott');

insert into order_dtl values (1,1,'Bread',20);

insert into order_dtl values (2,1,'Butter',50);

insert into order_dtl values (3,1,'Biscutes',15);

insert into order_mst values (2,sysdate, 'Tiger');

insert into order_dtl values (4,2,'Cream',20);

insert into order_dtl values (5,2,'Roll',50);

insert into order_dtl values (6,2,'Biscutes',15);

commit;

As we have inserted above sample data, let's check the same.

At this point we are good. Data is reflecting and having parent-child relationship too.
Now, what if i truncate order_mst table?

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


So, it is not allowing to truncate data as child table is having some reference data in it.
Now, let's try cascade option with the same command.

SQL> truncate table order_mst cascade;
Table truncated.

SQL> select * from order_mst;
no rows selected

SQL> select * from order_dtl;
no rows selected

Oh, Waaw, cascade truncated order master and detail both the tables.

Hope this small demo will help you to understand truncate cascade option.

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert