删除所有外键约束 禁用所有外键约束 启用所有外键约束

删除所有外键约束
Sql代码 复制代码
select 'alter table '||table_name||' drop constraint '||constraint_name||';' from user_constraints where constraint_type='R'

禁用所有外键约束
Sql代码
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type='R' 

启用所有外键约束
Sql代码
select 'alter table '||table_name||' enable constraint '||constraint_name||';' from user_constraints where constraint_type='R'  

 

查询所有的约束

 

select * from user_constraints where constraint_name=upper('FK_CT_FEERU_REFERENCE_CT_BILLC')

 

select * from user_cons_columns where lower(column_name)='numimprateguid'

 

 

使此外键不可用

alter table ct_trunkg_ref_billc disable constraint FK_CT_TRUNK_REFERENCE_CT_BILLC;

使此外键可用
alter table ct_trunkg_ref_billc enable constraint FK_CT_TRUNK_REFERENCE_CT_BILLC;

 

 

 

--ct_billcode与生产同步数据

select * from user_constraints where constraint_name=upper('FK_CT_FEERU_REFERENCE_CT_BILLC')
--第一步，停用引用-ct_billcode约束
alter table ct_trunkg_ref_billc disable constraint FK_CT_TRUNK_REFERENCE_CT_BILLC;
alter table CT_FEERULE disable constraint FK_CT_FEERU_REFERENCE_CT_BILLC;

--第二步，删除原有数据，同步生关数据
delete from ct_billcode

insert into ct_billcode
select * from ct_billcode@prod2

--第三步，约束从新建立
alter table ct_trunkg_ref_billc enable constraint FK_CT_TRUNK_REFERENCE_CT_BILLC;
alter table CT_FEERULE enable constraint FK_CT_FEERU_REFERENCE_CT_BILLC;