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); 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
You can check the child table details using : Get child table details
OWNER CHILD_TABL STATUS FK_COLUMN CONSTRAINT_NAME ---------- ---------- -------- --------------- --------------------------- TEST DBACHILD ENABLED EMPID CHILD_FK
Truncate the parent table with cascade option:
SQL> truncate table dbatest cascade; Table truncated. SQL> select count(*) from dbatest; COUNT(*) ---------- 0 SQL> select count(*) from dbachild; COUNT(*) ---------- 0
We can see the child table has been truncated, though we just truncated only parent table.
