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,
create table order_dtl
(ordtl_id number primary key,
ord_id number REFERENCES order_mst (ord_id) on delete cascade,
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);
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;
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,
Oracle Performance Tuning Certified Expert