欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

oracle 12c truncate table cascade记录

程序员文章站 2022-04-14 10:29:16
oracle 12c truncate table cascade记录 --------------------------------------------11g--------------...

oracle 12c truncate table cascade记录

--------------------------------------------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;

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> 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;

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;

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> truncate table t_parent cascade
  2  /

table truncated.