8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
TRUNCATE TABLE ... CASCADE in Oracle Database 12c Release 1 (12.1)
Oracle 12c introduced the CASCADE
clause of the TRUNCATE TABLE
command, allowing you to recursively truncate down the tables in a hierarchy. The functionality requires all referential integrity constraints involved to be defined using the ON DELETE CASCADE
clause.
Related articles.
Setup
The following code creates three tables in a grandparent-parent-child relationship. All the referential integrity constraints are defined using the ON DELETE CASCADE
clause. All of the foreign key columns are optional.
-- Create the test tables using ON DELETE CASCADE drop table t3 purge; drop table t2 purge; drop table t1 purge; create table t1 ( id number, description varchar2(50), constraint t1_pk primary key (id) ); create table t2 ( id number, t1_id number, description varchar2(50), constraint t2_pk primary key (id), constraint t2_t1_fk foreign key (t1_id) references t1 (id) on delete cascade ); create table t3 ( id number, t2_id number, description varchar2(50), constraint t3_pk primary key (id), constraint t3_t2_fk foreign key (t2_id) references t2 (id) on delete cascade );
Since the foreign key columns are optional, rows in the dependent tables can have a null value and not be part of the relationship. We'll take advantage of this and insert some data for each table. Notice that T2 and T3 both have a row that relates back to their respective parent and a row with a null value in the foreign key column.
-- Insert a data into each table. begin insert into t1 values (1, 't1 ONE'); insert into t2 values (1, 1, 't2 ONE'); insert into t2 values (2, null, 't2 TWO'); insert into t3 values (1, 1, 't3 ONE'); insert into t3 values (2, null, 't3 TWO'); commit; end; / -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 1 2 2 SQL>
DELETE ...
The presence of the ON DELETE CASCADE
relationships allows us to delete from any of the tables, with any dependent child records deleted automatically.
delete from t1; -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 0 1 1 SQL>
Notice the rows with null values in the foreign key columns are not deleted, as strictly speaking they were not orphaned by the initial deletion.
TRUNCATE ... CASCADE
Rollback the previous deletion to return the data to its original state.
rollback; -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 1 2 2 SQL>
A normal TRUNCATE
command fails with the following error.
truncate table t1; truncate table t1 * ERROR at line 1: ORA-02266: unique/primary keys in table referenced by enabled foreign keys SQL>
The TRUNCATE TABLE ... CASCADE
command succeeds and recursively truncates all the dependent tables.
truncate table t1 cascade; -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 0 0 0 SQL>
Notice all rows have been removed from the dependent tables, even those that were not related to rows in their parent table. It's worth noting this difference between the DELETE CASCADE
and the TRUNCATE CASCADE
commands.
As you would expect, a ROLLBACK
has no effect on a TRUNCATE
.
rollback; -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 0 0 0 SQL>
Aside
Both the DELETE CASCADE
and the TRUNCATE CASCADE
fail if any of the relationships in the hierarchy are not defined with the ON DELETE CASCADE
clause. To see this, recreate the T3 table as follows and populate the tables again.
-- Recreate T3. drop table t3 purge; create table t3 ( id number, t2_id number, description varchar2(50), constraint t3_pk primary key (id), constraint t3_t2_fk foreign key (t2_id) references t2 (id) ); -- Insert a data into each table. begin insert into t1 values (1, 't1 ONE'); insert into t2 values (1, 1, 't2 ONE'); insert into t2 values (2, null, 't2 TWO'); insert into t3 values (1, 1, 't3 ONE'); insert into t3 values (2, null, 't3 TWO'); commit; end; / -- Check the contents of the tables. select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 1 2 2 SQL>
As expected, both the DELETE CASCADE
and TRUNCATE CASCADE
fail.
delete from t1; delete from t1 * ERROR at line 1: ORA-02292: integrity constraint (TEST.T3_T2_FK) violated - child record found SQL> truncate table t1 cascade; truncate table t1 cascade * ERROR at line 1: ORA-14705: unique or primary keys referenced by enabled foreign keys in table "TEST"."T3" SQL>
If we clear down the rows in T3, the TRUNCATE CASCADE
still fails, but the DELETE CASCADE
works as expected.
-- Clear down T3. truncate table t3; select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 1 2 0 SQL> -- Truncate still fails. truncate table t1 cascade; truncate table t1 cascade * ERROR at line 1: ORA-14705: unique or primary keys referenced by enabled foreign keys in table "TEST"."T3" SQL> -- Deletion works as expected. delete from t1; select (select count(*) from t1) as t1_count, (select count(*) from t2) as t2_count, (select count(*) from t3) as t3_count from dual; T1_COUNT T2_COUNT T3_COUNT ---------- ---------- ---------- 0 1 0 SQL>
For more information see:
Hope this helps. Regards Tim...