8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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 heirarchy. The functionality requires all referential integrity constraints involved to be defined using the ON DELETE CASCADE clause.

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.
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;


-- 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 ... [CASCADE]

The presence of the ON DELETE CASCADE relationships allows us to delete from any of the tables, with any dependent child records deleted automatically. The CASCADE keyword in the following delete example is not really necessary, but it's good to use it to remind any other developers that you are expecting a recursive delete.

DELETE FROM 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          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.
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;


-- 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...

Back to the Top.