12c trancate table cascade

最新推荐文章于 2024-06-25 13:02:35 发布

朝闻道-夕死可矣

最新推荐文章于 2024-06-25 13:02:35 发布

阅读量3k

点赞数

分类专栏： -- Ora12c&13c

本文链接：https://blog.csdn.net/jc_benben/article/details/51654982

版权

-- Ora12c&13c 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

delete/truncate cascade

SQL> create table t1(id int,descs varchar2(10),
  2  constraint pk_t1 primary key (id));

Table created

SQL> create table t2(id int,t1_id int,name varchar2(10),
  2  constraint pk_t2 primary key (id),
  3  constraint pk_t2_t1 foreign key (t1_id) references t1(id) on delete cascade);
 
Table created
 
SQL> create table t3(id int,t2_id int,name varchar2(10),
  2  constraint pk_t3 primary key (id),
  3  constraint fk_t3_t2 foreign key (t2_id) references t2(id) on delete cascade);
 
Table created
 
SQL> insert into t1 values(1,'china');
 
1 row inserted
 
SQL> insert into t2 values(1,1,'beijing');
 
1 row inserted
 
SQL> insert into t2 values(2,null,'menggu')
  2  ;
 
1 row inserted
 
SQL> insert into t3 values(1,1,'chaoyangqu');
 
1 row inserted
 
SQL> insert into t3 values(2,null,'wulanbatuo');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;
 
  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         1          2          2
 
SQL> delete t1 cascade;
 
1 row deleted
 
SQL> 
SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;
 
  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         0          1          1
 
SQL> rollback;
 
Rollback complete
 
SQL> 
SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;
 
  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         1          2          2
 
SQL> delete from t1 cascade;
 
1 row deleted
 
SQL> 
SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;
 
  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         0          1          1
 
SQL> rollback;
 
Rollback complete
 
SQL> truncate table t1;
 
truncate table t1
 
ORA-02266: 表中的唯一/主键被启用的外键引用
 
SQL> truncate table t1 cascade;
 
truncate table t1 cascade
 
ORA-03291: 无效的截断选项 - 缺失 STORAGE 关键字
 
-- 如果在12c中执行，正常
SQL> select con_id,dbid,name,open_mode from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         3 2066919739 PDB1                           READ WRITE

SQL> create table t1(id int,descs varchar2(10),
  2  constraint pk_t1 primary key (id));
create table t1(id int,descs varchar2(10),
             *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用


SQL> drop table t1;

表已删除。

SQL> drop table t2;
drop table t2
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> create table t1(id int,descs varchar2(10),
  2  constraint pk_t1 primary key (id));

表已创建。

SQL> create table t2(id int,t1_id int,name varchar2(10),
  2  constraint pk_t2 primary key (id),
  3  constraint pk_t2_t1 foreign key (t1_id) references t1(id) on delete cascade
);

表已创建。

SQL> create table t3(id int,t2_id int,name varchar2(10),
  2  constraint pk_t3 primary key (id),
  3  constraint fk_t3_t2 foreign key (t2_id) references t2(id) on delete cascade
);

表已创建。

SQL> insert into t1 values(1,'china');

已创建 1 行。

SQL> insert into t2 values(1,1,'beijing');

已创建 1 行。

SQL>  insert into t2 values(2,null,'menggu')
  2  ;

已创建 1 行。

SQL> insert into t3 values(1,1,'chaoyangqu');

已创建 1 行。

SQL> insert into t3 values(2,null,'wulanbatuo');

已创建 1 行。

SQL> commit;

提交完成。

SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;

  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         1          2          2

SQL> delete table t1 cascade;
delete table t1 cascade
       *
第 1 行出现错误:
ORA-00903: 表名无效


SQL> delete from t1 cascade;

已删除 1 行。

SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;

  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         0          1          1

SQL> rollback;

回退已完成。

SQL> truncate table t1 cascade;

表被截断。

SQL> SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
  2         (SELECT COUNT(*) FROM t2) AS t2_count,
  3         (SELECT COUNT(*) FROM t3) AS t3_count
  4  FROM   dual;

  T1_COUNT   T2_COUNT   T3_COUNT
---------- ---------- ----------
         0          0          0
-- 注意，如果表t3中的约束是非cascade，也会出现错误
SQL> alter table t3 drop constraint fk_t3_t2;

表已更改。

SQL> alter table t3 add constraint fk_t3_t2 foreign key (t2_id) references t2(id
);

表已更改。

SQL> insert into t1 values(1,'china');

已创建 1 行。

SQL> insert into t2 values(1,1,'beijing');

已创建 1 行。

SQL> insert into t3 values(1,1,'chaoyangqu');

已创建 1 行。

SQL> commit;

提交完成。

SQL> truncate table t1 cascade;
truncate table t1 cascade
               *
第 1 行出现错误:
ORA-14705: 唯一关键字或主键由表 "SYS"."T3" 中的已启用外键引用