• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Tag Archives: Truncate Table

ORACLE 12c Release 1 – Truncate Cascade

17 Tuesday Sep 2013

Posted by troroblog in Uncategorized

≈ Leave a comment

Tags

ORACLE, ORACLE 12c Release 1, SQL, Truncate Table

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

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...