DBA Hub

📋Steps in this guide1/4

TRUNCATE TABLE ... CASCADE in Oracle Database 12c Release 1 (12.1)

This article discusses the TRUNCATE TABLE ... CASCADE functionality in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Setup

The following code creates three tables in a grandparent-parent-child relationship. All the referential integrity constraints are defined using the clause. All of the foreign key columns are optional. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- 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
);

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

DELETE ...

The presence of the relationships allows us to delete from any of the tables, with any dependent child records deleted automatically. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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>
3

TRUNCATE ... CASCADE

Rollback the previous deletion to return the data to its original state. A normal command fails with the following error. The command succeeds and recursively truncates all the dependent tables. 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 and the commands. As you would expect, a has no effect on a .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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>

truncate table t1;
truncate table t1
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL>

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>

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

Aside

Both the and the fail if any of the relationships in the hierarchy are not defined with the clause. To see this, recreate the T3 table as follows and populate the tables again. As expected, both the and fail. If we clear down the rows in T3, the still fails, but the works as expected. For more information see: - TRUNCATE TABLE - TRUCATE TABLE ... CASCADE Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
-- 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>

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>

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!