oracle 12c truncate table cascade 笔记

最新推荐文章于 2025-01-31 15:22:38 发布

bjchangxiao

最新推荐文章于 2025-01-31 15:22:38 发布

阅读量1.8k

点赞数 1

本文链接：https://blog.csdn.net/bjchangxiao/article/details/80939652

版权

本文对比了Oracle 11g与12c中使用级联删除约束时的行为差异，包括在删除父表记录时对子表的影响、外键约束的修改及级联截断操作的不同表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--------------------------------------------11g-----------------------------------------------------------
drop table t_child;
drop table t_parent;

create table t_parent(id number,name varchar2(20));
alter table t_parent add constraint pk_t_parent primary key(id);
create table t_child(id number ,name varchar2(20),p_id number);
alter table t_child add constraint pk_t_child primary key(id);
alter table t_child add constraint fk_t_parent foreign key(p_id) references t_parent(id) on delete cascade;
insert into t_parent values(1,'p1');
insert into t_child values(1,'c1',1);
commit;
SQL> delete from t_parent;

已删除 1 行。

SQL> commit;

提交完成。

-------------------------------------------------------------------------------------------------------------

alter table t_child drop constraint fk_t_parent;
alter table t_child add constraint fk_t_parent foreign key(p_id) references t_parent(id);
insert into t_parent values(1,'p1');
insert into t_child values(1,'c1',1);
commit;
delete from t_parent;

SQL> delete from t_parent;
delete from t_parent
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_T_PARENT) - 已找到子记录

-------------------------------------------------------------------------------------------------------------
drop table t_child;
drop table t_parent;

SQL> truncate table t_parent;
truncate table t_parent
*
第 1 行出现错误:
ORA-02266: 表中的唯一/主键被启用的外键引用

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

===============================================================================================================
--------------------------------------------12c----------------------------------------------------------------
drop table t_child;
drop table t_parent;

已删除 1 行。

SQL> commit;

提交完成。

---------------------------------------------------------------------------------------------------------------
alter table t_child drop constraint fk_t_parent;
alter table t_child add constraint fk_t_parent foreign key(p_id) references t_parent(id);
insert into t_parent values(1,'p1');
insert into t_child values(1,'c1',1);
commit;
delete from t_parent;

SQL> delete from t_parent;
delete from t_parent
*
第 1 行出现错误:
ORA-02292: 违反完整约束条件 (SCOTT.FK_T_PARENT) - 已找到子记录
---------------------------------------------------------------------------------------------------------------
drop table t_child;
drop table t_parent;