关于truncate cascade 操作测试

一、参考官方文档语法说明

11g 语法：

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10007.htm#i2067571

 

12.1 之后语法：

https://docs.oracle.com/database/121/SQLRF/statements_10007.htm#i2067571

可以看到，12.1 之后，truncate 语法后面多了cascade 选项。

二、存在主外键关系的表truncate操作测试

11g 版本测试

--1) 创建主外键关系表
SQL> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(1000));

Table created.

SQL> ALTER TABLE T_P ADD CONSTRAINT  T_P_ID_PK  PRIMARY KEY (ID);

Table altered.

SQL> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(1000));

Table created.

SQL> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID) ;

Table altered.


--2) 只插入父表数据，不插入子表数据
SQL> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

1985 rows created.

SQL> COMMIT;

Commit complete.

SQL> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> truncate table t_p cascade;
truncate table t_p cascade
                   *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword

[oracle@ora11204 ~]$ oerr ora 2266
02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"
// *Cause: An attempt was made to truncate a table with unique or
//         primary keys referenced by foreign keys enabled in another table.
//         Other operations not allowed are dropping/truncating a partition of a
//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.
// *Action: Before performing the above operations the table, disable the
//          foreign key constraints in other tables. You can see what
//          constraints are referencing a table by issuing the following
//          command:
//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";


--3) 继续插入子表数据，在执行truncate
SQL> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME  FROM ALL_OBJECTS;

80953 rows created.

SQL> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SQL> truncate table t_p cascade;
truncate table t_p cascade
                   *
ERROR at line 1:
ORA-03291: Invalid truncate option - missing STORAGE keyword


--4) 删除子表数据，在执行truncate操作
SYS@nocdb> truncate table t_c;

Table truncated.

SYS@nocdb> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

结论：在11g 版本中，存在主外键关系的表，无论子表和父表中是否存在数据，父表都不支持truncate 操作。且11g 版本中，truncate 后面没有cascade选项。 

12c 版本测试

场景1： 创建外键，不指定on delete 参数

--1) 创建主外键关系表
SYS@nocdb> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_P ADD CONSTRAINT  T_P_ID_PK  PRIMARY KEY (ID);

Table altered.

SYS@nocdb> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID);

Table altered.

--2) 只插入父表数据，不插入子表数据
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;

Table truncated.

--3) 继续插入子表数据，在执行truncate
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME  FROM ALL_OBJECTS;

71469 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SYS@nocdb> truncate table t_p cascade;
truncate table t_p cascade
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SYS"."T_C"

--4) 删除子表数据，在执行truncate操作
SYS@nocdb> truncate table t_c;

Table truncated.

SYS@nocdb> truncate table t_p;

Table truncated.

结论：创建外键不指定on delete 参数时候， 若子表中没有数据，那么父表可以直接truncate，若子表中存在数据，那么父表不允许truncate，即使加上cascade参数也不行。

场景2：创建外键，指定on delete set null

--1) 创建主外键关系表
SYS@nocdb> drop table t_p cascade constraints purge;

Table dropped.

SYS@nocdb> drop table t_c cascade constraints purge;

Table dropped.

SYS@nocdb> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_P ADD CONSTRAINT  T_P_ID_PK  PRIMARY KEY (ID);

Table altered.

SYS@nocdb> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID) on delete set null;

Table altered.

--2) 只插入父表数据，不插入子表数据
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;

Table truncated.

--3) 继续插入子表数据，在执行truncate
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME  FROM ALL_OBJECTS;

71469 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SYS@nocdb> truncate table t_p cascade;
truncate table t_p cascade
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SYS"."T_C"

--4) 删除子表数据，在执行truncate操作
SYS@nocdb> truncate table t_c;

Table truncated.

SYS@nocdb> truncate table t_p;

Table truncated.

结论：和场景1 不指定on delete参数结论一样。 

场景3：创建外键，指定on delete cascade

--1) 创建主外键关系表
SYS@nocdb> drop table t_p cascade constraints purge;

Table dropped.

SYS@nocdb> drop table t_c cascade constraints purge;

Table dropped.

SYS@nocdb> CREATE TABLE T_P (ID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_P ADD CONSTRAINT  T_P_ID_PK  PRIMARY KEY (ID);

Table altered.

SYS@nocdb> CREATE TABLE T_C (ID NUMBER, FID NUMBER, NAME VARCHAR2(1000));

Table created.

SYS@nocdb> ALTER TABLE T_C ADD CONSTRAINT FK_T_C FOREIGN KEY (FID) REFERENCES T_P (ID) on delete cascade;

Table altered.

--2) 只插入父表数据，不插入子表数据
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;

Table truncated.

--3) 继续插入子表数据，在执行truncate
SYS@nocdb> INSERT INTO T_P SELECT ROWNUM, TABLE_NAME FROM ALL_TABLES;

2213 rows created.

SYS@nocdb> INSERT INTO T_C SELECT ROWNUM, MOD(ROWNUM, 1000) + 1, OBJECT_NAME  FROM ALL_OBJECTS;

71469 rows created.

SYS@nocdb> COMMIT;

Commit complete.

SYS@nocdb> truncate table t_p;
truncate table t_p
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

SYS@nocdb> truncate table t_p cascade;

Table truncated.

SYS@nocdb> select count(*) from t_p;

  COUNT(*)
----------
         0

SYS@nocdb> select count(*) from t_c;

  COUNT(*)
----------
         0

结论：创建外键指定on delete cascade参数时候， 若子表中没有数据，那么父表可以直接truncate，若子表中存在数据，那么父表不允许truncate，但是加上cascade参数后，truncate操作成功，且连体子表中数据一起被truncate。

三、结论

综上测试可以看到：

• 在11g 版本中，无论子表中是否存在数据，父表都不可以执行truncate操作。
• 在12c 版本中，不管是否指定on delete参数，只要子表中没有数据，那么父表就可以执行truncate操作，这点比11g 有所增强。
• 在12c 版本中，只有指定了on delete cascade参数，那么在子表存在数据的情况下，可以通过truncate cascade 命令同时删除父表和子表中数据。