ORACLE外键启用禁用

--禁用所有外键

DECLARE
   esql varchar2(3000);
begin 
 for cur in ( select 'alter table '|| t.table_name||' disable constraint '||t.constraint_name  as comment_sql
  from user_constraints t where t.constraint_type = 'R'  
  order by t.table_name) loop
  
         esql := cur.comment_sql;
         EXECUTE IMMEDIATE esql;
  end loop; 
end; 

--启用所有外键
DECLARE
   esql varchar2(3000);
begin 
 for cur in ( select 'alter table '|| t.table_name ||' enable constraint '||t.constraint_name as comment_sql
from user_constraints t where t.constraint_type = 'R'  
order by t.table_name ) loop
  
         esql := cur.comment_sql;
         EXECUTE IMMEDIATE esql;
  end loop; 
end;   


--删除所有约束
declare 
       v_owner varchar2(20):= 'GZ';
begin  

  --删除外键

  for cur_1 in( select b.table_name,a.column_name,b.constraint_name
         from user_cons_columns a, user_constraints b 
         where a.constraint_name = b.constraint_name 
         and b.constraint_type = 'R' and a.owner= v_owner) 
  loop
        --dbms_output.put_line('alter table ' || cur_1.table_name || ' drop constraint ' || cur_1.constraint_name);
        execute immediate  'alter table ' || cur_1.table_name || ' drop constraint ' || cur_1.constraint_name;
  end loop;
 
  --删除主键
  for cur_2 in(
              select b.table_name,a.column_name,b.constraint_name
         from user_cons_columns a, user_constraints b 
         where a.constraint_name = b.constraint_name 
         and b.constraint_type = 'P' and a.owner=v_owner and a.position  =1
   )
  loop
      execute immediate  'alter table ' || cur_2.table_name || ' drop constraint ' || cur_2.constraint_name;
  end loop;
  
 --删除唯一健
 for cur_3 in( select a.column_name ,b.table_name,a.constraint_name
         from user_cons_columns a, user_constraints b 
         where a.constraint_name = b.constraint_name 
         and b.constraint_type = 'U' and a.owner=v_owner and a.position  =1) 
  loop
 
      execute immediate  'alter table ' || cur_3.table_name || ' drop constraint ' || cur_3.constraint_name;
  end loop;
 
  
  --删除索引      
    for cur in (select index_name
                  from user_indexes t
                 where t.table_owner = v_owner AND T.index_type='NORMAL') loop
        execute immediate 'drop index ' || cur.index_name;
    end loop;
end;