Prior to ORACLE 12c truncating a table referenced by enabled foreign keys was no possible (even if the tables were empty).

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

ORACLE 12c adds a new option to the TRUNCATE command

• Parent and all children tables will be truncated if the foreign key between (all of) them is defined as ON DELETE CASCADE
• Even if no parent exists (optional foreign key)
• Even if the foreign key is defined to be novalidate
• It is a truncate not a delete

Example

REM ===========================================================================
REM Create tables
REM ===========================================================================
CREATE TABLE dept_trunc_casc_demo (
   deptno NUMBER
  ,dname  VARCHAR2(30)
  ,CONSTRAINT dept_trunc_cast_demo_pk PRIMARY KEY (deptno)
  )
/


CREATE TABLE emp_trunc_casc_demo (
   empno  NUMBER
  ,ename  VARCHAR2(30)
  ,deptno NUMBER
  ,CONSTRAINT emp_trunc_casc_demo_pk PRIMARY KEY (empno)
  ,CONSTRAINT emp_dept_trunc_casc_fk FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo
  )
/


REM ===========================================================================
REM Add data
REM ===========================================================================
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (10,'ACCOUNTING');
INSERT INTO dept_trunc_casc_demo (deptno, dname) VALUES (20,'SALES');

INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (1,'KUNZ',10);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (2,'MEIER',20);
INSERT INTO emp_trunc_casc_demo (empno, ename, deptno) VALUES (3,'HUBER',20);

COMMIT;

REM ===========================================================================
REM Trying to truncate master table
REM Will not work as with prior versions of ORACLE
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo;

TRUNCATE TABLE dept_trunc_casc_demo
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

REM ===========================================================================
REM Trying to truncate master table with new keyword CASCADE
REM This will not work unless there is a FK with on delete cascade.
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo CASCADE;

TRUNCATE TABLE dept_trunc_casc_demo CASCADE;
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "O12TEST"."EMP_TRUNC_CASC_DEMO"

REM ===========================================================================
REM Changing the foreign key to cascade delete
REM ===========================================================================
ALTER TABLE emp_trunc_casc_demo DROP CONSTRAINT emp_dept_trunc_casc_fk;

ALTER TABLE emp_trunc_casc_demo ADD CONSTRAINT emp_dept_trunc_casc_fk
   FOREIGN KEY (deptno) REFERENCES dept_trunc_casc_demo ON DELETE CASCADE;

REM ===========================================================================
REM Truncating Master Table with cascade keyword
REM ===========================================================================
TRUNCATE TABLE dept_trunc_casc_demo cascade;

REM ===========================================================================
REM Show number of rows in child table
REM ===========================================================================

SELECT COUNT(*)
  FROM emp_trunc_casc_demo;

COUNT(*)
--------
       0