Cascaded truncate
In versions prior to 12c, truncation of a master table was not provided when the child table referred to a master table and where there were records in the child table. On the other hand, the TRUNCATE TABLE with CASCADE operation in 12c can truncate the records in the main table, recursively truncate the child tables automatically, and refer to them as DELETE ON CASCADE obeying foreign keys. Because this applies to all child tables, there is no CAP for the number of recursive levels, which can be grandchild tables, great-grandchild tables, and so on. This enhancement rejects the premise of truncating all child table records before truncating a master table. The new CASCADE statement can also be applied to table partitions and child table partitions.
SQL > create table parent (id number primary key)
Table created.
SQL > create table child (cid number primary key,id number)
Table created.
SQL > insert into parent values (1)
1 row created.
SQL > insert into parent values (2)
1 row created.
SQL > insert into child values (1Pol 1)
1 row created.
SQL > insert into child values (2jue 1)
1 row created.
SQL > insert into child values (3jue 2)
1 row created.
SQL > commit
Commit complete.
SQL > select a. ID from parent a b. Cidre b. CI., child b where a.id=b.id
ID CID ID 1 1 1 2 1 2 3 2
-- add constraints without on delete cascade
SQL > alter table child add constraint fk_parent_child foreign key (id) references parent (id)
Table altered.
SQL > truncate table parent cascade
Truncate table parent cascade
*
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table
"HR." CHILD.
SQL > col CONSTRAINT_NAME for A25
SQL > col TABLE_NAME for A25
SQL > col COLUMN_NAME for A25
SQL > select CONSTRAINT_NAME,TABLE_NAME, COLUMN_NAME from user_cons_columns where TABLE_NAME='CHILD'
CONSTRAINT_NAME TABLE_NAME COLUMN_NAME
SYS_C0010458 CHILD CID
FK_PARENT_CHILD CHILD ID
-- remove and add constraints with on delete cascade attached
SQL > alter table child drop constraint FK_PARENT_CHILD
Table altered.
SQL > alter table child add constraint fk2_parent_child foreign key (id) references parent (id) on delete cascade
Table altered.
SQL > truncate table parent cascade
Table truncated.
SQL > select a. ID from parent a b. Cidre b. CI., child b where a.id=b.id
No rows selected
