12c truncate cascade

从12c开始，truncate命令支持了新的参数cascade

有了这个参数你就可以同时truncate父表和子表，当然这个参数的使用有一定的前提条件，就是父子表的完整性约束必须要用ON DELETE CASCADE来定义，父子表必须要属于同一个用户下，同时你要对这些表都有DROP TABLE的权限

新建一对父子表，用ON DELETE CASCADE来定义相关约束

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE t1 (
  id           NUMBER,
  name  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id             NUMBER,
  t1_id          NUMBER,
  name    VARCHAR2(50),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
);

由于外键字段是可选的，所有子表可以允许有NULL数据不在对应的关系范围内，所以插入部分数据

1
2
3
4
5
6
insert into t1 values(1,'xb');

insert into t2 values(1,1,'xb');
insert into t2 values(2,null,'xb2');

commit;

因为在foreign key上指定了ON DELETE CASCADE,所以删除t1表时，会同时删除T2表中的关联数据

1
2
3
4
5
6
7
8
9
SYS@ora12c> delete from t1;

1 row deleted.

SYS@ora12c> select * from t2;

        ID      T1_ID NAME
---------- ---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
         2            xb2

注意到因为T1_ID为null值的记录在主表中并没有关联数据，所以在删除主表的时候这条数据并未收到影响

测试truncate

如果按照以往的truncate语法，直接进行清空主表，则会报错ORA-02266

1
2
3
4
5
6
7
8
9
SYS@ora12c> rollback;

Rollback complete.

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

加上cascade参数

1
2
3
4
5
6
7
SYS@ora12c> truncate table t1 cascade;

Table truncated.

SYS@ora12c> select * from t2;

no rows selected

可以看到主表顺利的清空，同时子表T2也被清空，包括之前那条T1_ID为NULL的数据，所以在执行truncate cascade的时候一定要注意到这一点

如果约束条件里没有定义ON DELETE CASCADE,那么不论DELETE TABLE或者TRUNCATE TABLE(CASCADE)都会失败

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DROP TABLE T1 CASCADE CONSTRAINTS;
DROP TABLE T2 CASCADE CONSTRAINTS;

CREATE TABLE t1 (
  id           NUMBER,
  name  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id             NUMBER,
  t1_id          NUMBER,
  name    VARCHAR2(50),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id)
);

insert into t1 values(1,'xb');

insert into t2 values(1,1,'xb');
insert into t2 values(2,null,'xb2');

commit;


SYS@ora12c> delete from t1;
delete from t1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.T2_T1_FK) violated - child record found


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


SYS@ora12c> truncate table t1 cascade;
truncate table t1 cascade
               *
ERROR at line 1:
ORA-14705: unique or primary keys referenced by enabled foreign keys in table "SYS"."T2"
12c新特性
12c truncate cascade
https://www.xbdba.com/2018/11/29/12c-truncate-cascade/
作者
xbdba
发布于
2018年11月29日
许可协议