DBA Hub

šŸ“‹Steps in this guide1/2

truncate table with cascade feature1 in oracle 12c - DBACLASS DBACLASS

In oracle 12c TRUNCATE TABLE CASCADE will truncate its child tables, if ON DELETE CASCADE relationship is present on child table. Create one parent and child table with some data SQL> CREATE TABLE DBATEST ( EMPNO number, NAME varchar2(10) , empid number); Table created. SQL> alter table dbatest add constraint emp_pk primary key ( empid); [ā€¦]

oracle clusteringintermediate
by OracleDba
13 views
1

Overview

In oracle 12c TRUNCATE TABLE CASCADE will truncate its child tables, if ON DELETE CASCADE relationship is present on child table. Create one parent and child table with some data You can check the child table details using : Get child table details ā€“ Truncate the parent table with cascade option:

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
SQL> CREATE TABLE DBATEST ( EMPNO number, NAME varchar2(10) , empid number);

Table created.
	
SQL> alter table dbatest add constraint emp_pk primary key ( empid);

Table altered.

													   NOT NULL 
SQL> CREATE TABLE DBACHILD ( EMPID number, dept number);

Table created.


--- add the delete cascade keyword while adding foreign key constraint

SQL> alter table dbachild add constraint child_fk foreign key ( EMPID ) REFERENCES dbatest(EMPID) on delete cascade;

Table altered.


SQL> insert into dbatest values ( 1,'RAJ',1);

1 row created.

SQL> insert into dbatest values ( 1,'RAM',2);

1 row created.

SQL> insert into dbatest values ( 1,'RAM',3);

1 row created.

SQL> commit;

Commit complete.


SQL> insert into dbachild values ( 1,2);

1 row created.

SQL> insert into dbachild values ( 2,2);

1 row created.

SQL> commit;     

Commit complete.

SQL> select count(*) from dbatest;

  COUNT(*)
----------
	 3

SQL> select count(*) from dbachild;

  COUNT(*)
----------
	 2

OWNER CHILD_TABL STATUS FK_COLUMN CONSTRAINT_NAME
---------- ---------- -------- --------------- ---------------------------
TEST DBACHILD ENABLED EMPID CHILD_FK
2

Section 2

We can see the child table has been truncated, though we just truncated only parent table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> truncate table dbatest cascade;

Table truncated.

SQL> select count(*) from dbatest;

  COUNT(*)
----------
	 0


SQL> select count(*) from dbachild;

  COUNT(*)
----------
	 0

Comments (0)

Please to add comments

No comments yet. Be the first to comment!