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