清空oracle表数据 外键,清空带外键的表数据

1，创建测试条件

create table t1(

t1_id number(10),

ti_a varchar2(10)

)

create table t2(

t2_id number(10),

t2_b varchar2(10)

);

alter table t1 add constraint PK_t1 primary key(t1_id);

alter table T2

add constraint FK_t2_t1 foreign key ("T2_ID")

references "T1" ("T1_ID");

insert into t1 values(1,'a');

insert into t1 values(2,'b');

insert into t2 values(1,'ta');

insert into t2 values(2,'tb');

commit;

2.禁用所有外键约束, 使用下面的sql生成对应sql脚本:

select 'alter table ' || table_name || ' disable constraint ' || constraint_name || ';' from user_constraints where constraint_type = 'R';

生成的sql类似下面的语句：

alter table T2 disable constraint FK_T2_T1;

执行删除

delete from t1;

3.启用所有外键约束, 使用下面的sql生成对应sql脚本:

select 'alter table ' || table_name || ' enable constraint ' || constraint_name || ';' from user_constraints where constraint_type = 'R';

alter table T2 enable constraint FK_T2_T1;

阅读(729) | 评论(0) | 转发(0) |