Search

Top 60 Oracle Blogs

Recent comments

Oracle 12c SQL – TRUNCATE TABLE … CASCADE

Oracle 12c updates the TRUNCATE TABLE statement allowing the use of CASCADE to eliminate values in tables that are referentially connected.

This option is something that some shops were looking for from Oracle; it is possible that your shop will not find the need for this option.

 

Here is the syntax:

#0000ff;"> TRUNCATE TABLE ID_TEST1 CASCADE;

(other TRUNCATE TABLE options such as those for MATERIALIZED VIEWs and/or STORAGE may still be specified)

 

When using TRUNCATE TABLE with CASCADE:

  • Child table referential security must specify “ON DELETE CASCADE” or the statement will fail
  • If layers of tables are referentially connected and all specify “ON DELETE CASCADE” the system will CASCADE as directed

 

Here is some example code:

 

*** Setting up sample tables ***

#0000ff;">SQL> --
#0000ff;">SQL> -- Example Truncate Cascade
#0000ff;">SQL> --
#0000ff;">SQL> drop table dept2;
#0000ff;">Table DEPT2 dropped.
#0000ff;">SQL> drop table emp2;
#0000ff;">Table EMP2 dropped.
#0000ff;">SQL> create table dept2 as select * from dept;
#0000ff;">Table DEPT2 created.
#0000ff;">SQL> create table emp2 as select * from emp;
#0000ff;">Table EMP2 created.
#0000ff;">SQL> alter table dept2 add constraint deptnoPK primary key (deptno);
#0000ff;">Table DEPT2 altered.
#0000ff;">SQL> alter table emp2 add constraint empnoPK primary key (empno);
#0000ff;">Table EMP2 altered.
#0000ff;">SQL> alter table emp2 add constraint deptnoFK foreign key (deptno)
#0000ff;">   references dept2
#0000ff;">   on delete cascade;
#0000ff;">Table EMP2 altered.
#0000ff;">SQL> select * from dept2;
#0000ff;">   DEPTNO  DNAME           LOC        
#0000ff;">---------- -------------- -------------
#0000ff;">       10  ACCOUNTING      NEW YORK    
#0000ff;">       20  RESEARCH        DALLAS      
#0000ff;">       30  SALES           CHICAGO    
#0000ff;">       40  OPERATIONS      BOSTON      
#0000ff;">SQL> select * from emp2;
#0000ff;">     EMPNO ENAME     JOB             MGR HIREDATE         SAL       COMM
#0000ff;">---------- ---------- --------- ---------- --------- ---------- ----------
#0000ff;">   DEPTNO
#0000ff;">----------
#0000ff;">     7369 SMITH     CLERK           7902 17-DEC-80       800          
#0000ff;">       20
#0000ff;">     7499 ALLEN     SALESMAN       7698 20-FEB-81       1600       300
#0000ff;">       30
#0000ff;">     7521 WARD       SALESMAN       7698 22-FEB-81       1250       500
#0000ff;">       30
#0000ff;">     7566 JONES     MANAGER         7839 02-APR-81       2975          
#0000ff;">       20
#0000ff;">     7654 MARTIN     SALESMAN       7698 28-SEP-81       1250       1400
#0000ff;">       30
#0000ff;">     7698 BLAKE     MANAGER        7839 01-MAY-81       2850          
#0000ff;">       30
#0000ff;">     7782 CLARK     MANAGER         7839 09-JUN-81       2450          
#0000ff;">       10
#0000ff;">     7788 SCOTT     ANALYST         7566 19-APR-87       3000          
#0000ff;">       20
#0000ff;">     7839 KING       PRESIDENT           17-NOV-81       5000          
#0000ff;">       10
#0000ff;">     7844 TURNER     SALESMAN       7698 08-SEP-81       1500         0
#0000ff;">       30
#0000ff;">     7876 ADAMS     CLERK           7788 23-MAY-87       1100          
#0000ff;">       20
#0000ff;">     7900 JAMES     CLERK           7698 03-DEC-81       950          
#0000ff;">       30
#0000ff;">     7902 FORD       ANALYST         7566 03-DEC-81       3000          
#0000ff;">       20
#0000ff;">     7934 MILLER     CLERK           7782 23-JAN-82       1300          
#0000ff;">       10
#0000ff;">14 rows selected

 

*** Show that Referential Integrity is working ***

#0000ff;">SQL> insert into emp2
#0000ff;">(empno,ename,hiredate,sal,deptno)
#0000ff;">values
#0000ff;">(4242,'BadRec',current_date,123.45,60);
#0000ff;">Error starting at line : 16 in command -
#0000ff;">insert into emp2
#0000ff;">(empno,ename,hiredate,sal,deptno)
#0000ff;">values
#0000ff;">(4242,'BadRec',current_date,123.45,60)
#0000ff;">Error report -
#0000ff;">SQL Error: ORA-02291: integrity constraint (JOHN.DEPTNOFK) violated - parent key not found
#0000ff;">00000 - "integrity constraint (%s.%s) violated - parent key not found"
#0000ff;">*Cause:   A foreign key value has no matching primary key value.
#0000ff;">*Action:   Delete the foreign key or add a matching primary key.
#0000ff;">SQL> --
#0000ff;">SQL> commit;
#0000ff;">Commit complete.

 

*** Show use of TRUNCATE CASCADE ***

#0000ff;">SQL> -- The following statement will fail; table has child rows
#0000ff;">SQL> truncate table dept2;
#0000ff;">Error starting at line : 23 in command -
#0000ff;">truncate table dept2
#0000ff;">Error report -
#0000ff;">SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
#0000ff;">00000 - "unique/primary keys in table referenced by enabled foreign keys"
#0000ff;">*Cause:   An attempt was made to truncate a table with unique or
#0000ff;">           primary keys referenced by foreign keys enabled in another table.
#0000ff;">           Other operations not allowed are dropping/truncating a partition of a
#0000ff;">           partitioned table or an ALTER TABLE EXCHANGE PARTITION.
#0000ff;">*Action:   Before performing the above operations the table, disable the
#0000ff;">           foreign key constraints in other tables. You can see what
#0000ff;">           constraints are referencing a table by issuing the following
#0000ff;">           command:
#0000ff;">           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";
#0000ff;">SQL> -- Command will succeed; child rows will be truncated too
#0000ff;">SQL> truncate table dept2 cascade;
#0000ff;">Table DEPT2 truncated.
#0000ff;">SQL> -- proof
#0000ff;">SQL> select * from dept2;
#0000ff;">no rows selected
#0000ff;">SQL> select * from emp2;
#0000ff;">no rows selected

As always, TRUNCATE should be use with full awareness that you cannot ROLLBACK a TRUNCATE, nor can you flash back to the state of the table before the truncate.

You may not TRUNCATE the parent table of a referenced-partition table; the reference-partitioned child must be dropped first.

 

Again this may not be for you; however, for some this is a really great new feature!