LATEST TOPICS

Oracle 12c: TRUNCATE can be cascaded down to the lowest level of hierarchy

Do you recall the following error?

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

Well, as you know this error encounters when we try to truncate a table which has unique/primary key and is referenced by a active foreign key from another table. The solution to this issue is to disable the active foreign keys in the referenced table before we can actually truncate the parent table. This is a time consuming task as we need to first identify all the tables and their foreign keys which has a reference to the primary key of the table to be truncated.

However, with Oracle 12c, we don’t need to do that extra work of identifying and disabling foreign keys to allow truncate on parent table. Oracle 12c has introduced an additional CASCADE clause for the TRUNCATE TABLE statement, which allows us to truncate a parent table by recursively truncating all the associated child tables. This new clause requires that the referential constraints must be defined with ON DELETE CASCADE option.

Let’s walk through a quick demonstration to have an insight in to this feature.

In this demonstration, I am creating three tables PARENT, CHILD and GRAND_CHILD with parent–>child–>grand_child relationship as shown below.

---//
---// parent table //--
---//
SQL> create table parent
  2  (
  3  p_id number,
  4  p_name varchar(20),
  5  constraint parent_pk primary key (p_id)
  6  );

Table created.

---//
---// child table (references parent table) //---
---//
SQL> create table child
  2  (
  3  c_id number,
  4  p_id number,
  5  c_name varchar(20),
  6  constraint child_pk primary key (c_id),
  7  constraint parent_child_fk foreign key (p_id) 
  8  references parent (p_id) on delete cascade
  9  );

Table created.

---//
---// grand_child table (references child table) //---
---//
SQL> create table grand_child
  2  (
  3  gc_id number,
  4  c_id number,
  5  gc_name varchar(20),
  6  constraint grand_child_pk primary key (gc_id),
  7  constraint gc_child_fk foreign key (c_id) 
  8  references child (c_id) on delete cascade
  9  );

Table created.

As you can observe, I have defined the referential constraints (parent_child_fk and gc_child_fk) with ON DELETE CASCADE option. This is a primary requirement for TRUNCATE..CASCADE command to work. Now, lets populate these tables with some data as shown below.

---//
---// populating parent table //---
---//
SQL> insert into parent values (&p_id,'&p_name');
Enter value for p_id: 1
Enter value for p_name: Amar
old   1: insert into parent values (&p_id,'&p_name')
new   1: insert into parent values (1,'Amar')

1 row created.

SQL> /
Enter value for p_id: 2
Enter value for p_name: Akbar
old   1: insert into parent values (&p_id,'&p_name')
new   1: insert into parent values (2,'Akbar')

1 row created.

SQL> /
Enter value for p_id: 3
Enter value for p_name: Anthony
old   1: insert into parent values (&p_id,'&p_name')
new   1: insert into parent values (3,'Anthony')

1 row created.

SQL> commit;

Commit complete.

---//
---// populating child table //---
---//
SQL> insert into child values (&c_id,&p_id,'&c_name');
Enter value for c_id: 1
Enter value for p_id: 1
Enter value for c_name: Arun
old   1: insert into child values (&c_id,&p_id,'&c_name')
new   1: insert into child values (1,1,'Arun')

1 row created.

SQL> /
Enter value for c_id: 2
Enter value for p_id: 2
Enter value for c_name: Sahid
old   1: insert into child values (&c_id,&p_id,'&c_name')
new   1: insert into child values (2,2,'Sahid')

1 row created.

SQL> /
Enter value for c_id: 3
Enter value for p_id: 3
Enter value for c_name: John
old   1: insert into child values (&c_id,&p_id,'&c_name')
new   1: insert into child values (3,3,'John')

1 row created.

SQL> commit;

Commit complete.

---//
---// populating grand_child table //---
---//
SQL> insert into grand_child values (&gc_id,&c_id,'&gc_name');
Enter value for gc_id: 1
Enter value for c_id: 1
Enter value for gc_name: Rahul
old   1: insert into grand_child values (&gc_id,&c_id,'&gc_name')
new   1: insert into grand_child values (1,1,'Rahul')

1 row created.

SQL> /
Enter value for gc_id: 2
Enter value for c_id: 2
Enter value for gc_name: Irfan
old   1: insert into grand_child values (&gc_id,&c_id,'&gc_name')
new   1: insert into grand_child values (2,2,'Irfan')

1 row created.

SQL> /
Enter value for gc_id: 3
Enter value for c_id: 3
Enter value for gc_name: Remo
old   1: insert into grand_child values (&gc_id,&c_id,'&gc_name')
new   1: insert into grand_child values (3,3,'Remo')

1 row created.

SQL> commit;

Commit complete.

We have populated the tables with following data

---//
---// data from parent table //---
---//
SQL> select * from parent;

      P_ID P_NAME
---------- --------------------
         1 Amar
         2 Akbar
         3 Anthony

---//
---// data from child table //---
---//
SQL> select * from child;

      C_ID       P_ID C_NAME
---------- ---------- --------------------
         1          1 Arun
         2          2 Sahid
         3          3 John

---//
---// data from grand child table //---
---//
SQL> select * from grand_child;

      GC_ID       C_ID GC_NAME
---------- ---------- --------------------
         1          1 Rahul
         2          2 Irfan
         3          3 Remo

---//
---// related data from all tables //---
---//		 
SQL> select p_name Parent, c_name Child, gc_name "Grand Child"
  2  from parent p, child c, grand_child g
  3  where p.p_id=c.p_id and c.c_id=g.c_id;

PARENT               CHILD                Grand Child
-------------------- -------------------- --------------------
Amar                 Arun                 Rahul
Akbar                Sahid                Irfan
Anthony              John                 Remo

Now, if we try to truncate the PARENT table, we would not be allowed as it has records which are referenced by the CHILD table records. Likewise, if we try to truncate CHILD table, it would not allow the same as it has records which are referenced by the GRAND_CHILD table records.

---//
---// truncate is not allowed on tables with active referenced keys //---
---//
SQL> truncate table parent;
truncate table parent
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys


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

Here comes the killer feature of 12c, the TRUNCATE command with CASCADE clause. When we truncate a table with the CASCADE option, Oracle recursively truncates all tables in the referential hierarchy (grand_parent, parent, child, grand_child, …) as show below.

---//
---// truncate table with cascade option //---
---//
SQL> truncate table parent cascade;

Table truncated.

---//
---// records after truncate //---
---// 
SQL> select * from parent;

no rows selected

SQL> select * from child;

no rows selected

SQL> select * from grand_child;

no rows selected

As we can observe, though we had just truncated the PARENT table, Oracle internally truncated CHILD and GRAND_CHILD table. This is due to the fact that the PARENT table was referenced by CHILD table which in turn was referenced by the GRAND_CHILD table.

With this new cool feature, there is a definite reason for DBAs to rejoice :)

Clef two-factor authentication
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed