Oracle 12c: Truncate Table Cascade


In this post, we will test the new “Truncate Table Cascade” command in 12c.

When you specify the “Truncate Table Cascade” all child tables will be truncated when they are referenced with a “DELETE ON CASCADE” option.

view plaincopy to clipboardprint?
Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.1.0
Connected as wissem

SQL>
SQL> DROP TABLE Tab_tst4_child PURGE
2 /

Table dropped
SQL> DROP TABLE Tab_tst4 PURGE
2 /

Table dropped

SQL>
SQL> CREATE TABLE Tab_tst4(COL1_ID NUMBER PRIMARY KEY, COL2_NAME INT)
2 PARTITION BY RANGE(COL2_NAME) INTERVAL (10)
3 (PARTITION PART1 VALUES LESS THAN (10));

Table created

SQL>
SQL> CREATE TABLE Tab_tst4_child(COL1_ID_FK INT NOT NULL, COL2_NAME INT,
2 CONSTRAINT COL1_ID1_FK FOREIGN KEY(COL1_ID_FK) REFERENCES Tab_tst4(COL1_ID) on delete CASCADE)
3 PARTITION BY REFERENCE(COL1_ID1_FK);

Table created

SQL>
SQL> INSERT INTO Tab_tst4 VALUES(10, 10);

1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(20, 20);

1 row inserted
SQL> INSERT INTO Tab_tst4 VALUES(30, 30);

1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(10, 12);

1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(20, 13);

1 row inserted
SQL> INSERT INTO Tab_tst4_child VALUES(30, 14);

1 row inserted

SQL> commit;

Commit complete

SQL> TRUNCATE TABLE Tab_tst4 CASCADE;

Table truncated

SQL> select * from Tab_tst4_child;

COL1_ID_FK COL2_NAME
————————————— —————————————

SQL> select * from Tab_tst4;

COL1_ID COL2_NAME
———- —————————————

SQL>
If you specify TRUNCATE TABLE … CASCADE for a child table not having ON DELETE CASCADE on the refetential constraint you will have “ORA-14705: unique or primary keys referenced by enabled foreign keys in table “WISSEM”.”TAB_TST4_CHILD””

view plaincopy to clipboardprint?
SQL> TRUNCATE TABLE Tab_tst4 CASCADE;

TRUNCATE TABLE Tab_tst4 CASCADE

ORA-14705: unique or primary keys referenced by enabled foreign keys in table “WISSEM”.”TAB_TST4_CHILD”

SQL>
In 11gR2, you will have the following error;

view plaincopy to clipboardprint?
SQL> TRUNCATE TABLE Tab_tst4 CASCADE;

TRUNCATE TABLE Tab_tst4 CASCADE

ORA-03291: Invalid truncate option – missing STORAGE keyword

SQL>
References ; Doc 12cR1

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s