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.