18 July 2013

Truncate table cascade in Oracle 12c

In Oracle 12c, the TRUNCATE TABLE CASCADE option is new. Here is a demo.


SCOTT@PDB01 > create table PARENT (
  2  ID number generated always as identity PRIMARY KEY,
  3  NAME VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > create table CHILD (
  2   ID number generated always as identity PRIMARY KEY,
  3  NAMECHILD VARCHAR2(20)
  4  );

Table created.

SCOTT@PDB01 > alter table CHILD add constraint FK_PARENT
  2  FOREIGN KEY (ID) references PARENT(ID) ON DELETE CASCADE;

Table altered.

SCOTT@PDB01 > insert into PARENT (NAME) values ('JOHN');

1 row created.

SCOTT@PDB01 > insert into CHILD (NAMECHILD) values ('JULIAN');

1 row created.

SCOTT@PDB01 > commit;

Commit complete.

SCOTT@PDB01 > select * from PARENT;

        ID NAME
---------- --------------------
         1 JOHN

SCOTT@PDB01 > select * from CHILD;

        ID NAMECHILD
---------- --------------------
         1 JULIAN

SCOTT@PDB01 > truncate table PARENT cascade;

Table truncated.

SCOTT@PDB01 > select * from PARENT;

no rows selected

SCOTT@PDB01 > select * from CHILD;

no rows selected

No comments:

Post a Comment