Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.