12c - Truncate Cascade

In Oracle Database 12c, there is a new option for the TRUNCATE command, CASCADE.

Unlike DELETE, the TRUNCATE TABLE command only changes the High Water Mark of the table, so it almost does not generate UNDO and is instantaneous.

SQL> truncate table scott.dept;

truncate table scott.dept

                     *

ERROR at line 1:

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

Seeking the constraint that references the table:

SQL> select owner, constraint_name, table_name, delete_rule 

from dba_constraints 

where (r_owner, r_constraint_name) = (SELECT owner, constraint_name from dba_constraints where owner = 'SCOTT' and table_name = "DEPT" and constraint_type = 'P');

OWNER CONSTRAINT_NAME TABLE_NAME DELETE_RULE

---------- -------------------- -------------------- --------------------

SCOTT FK_DEPTNO        EMP        NO ACTION

Using the CASCADE:

SQL> truncate table scott.dept cascade;

truncate table scott.dept cascade

                     *

ERROR at line 1:

ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SCOTT" "EMP".

The above error occurred because the constraint is not an ON DELETE SHELL OF.

We will change the type of constraint:

SQL> ALTER TABLE SCOTT.EMP

  DROP CONSTRAINT FK_DEPTNO; 

Table altered.

SQL> ALTER TABLE SCOTT.EMP ADD (

  CONSTRAINT FK_DEPTNO

  FOREIGN KEY (DEPTNO)

  REFERENCES SCOTT.DEPT (DEPTNO) on delete cascade);

Table altered.

Running the command:

SQL> truncate table scott.dept cascade;

Table truncated.

The data was successfully deleted:

SQL> select * from scott.dept;

no rows selected

SQL> select * from SCOTT.EMP;

no rows selected