oracle 12c truncate table cascade记录

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.