禁用与特定表关联的所有外键约束

3 回答

• 2

  假设你有这样的表格：

  create table MY_TABLE ( CODE number primary key);
  create table anotherTable ( code_ref number);
  alter table  anotherTable add constraint ck1 foreign key ( code_ref) references my_table ( code);
  create table yetAnotherTable ( code_ref number);
  alter table  yetAnotherTable add constraint ck2 foreign key ( code_ref) references my_table ( code);
  

  您可以使用类似以下内容来循环遍历引用表的给定列的所有约束，并使用以下命令禁用/启用它们：

  begin
      for s in (
                  SELECT 'alter table ' || c2.table_name || ' modify constraint ' || c2.constraint_name || ' disable' as statement
                  FROM all_constraints c
                         INNER JOIN all_constraints c2
                           ON ( c.constraint_name = c2.r_constraint_name AND c.owner = c2.owner)
                         INNER JOIN all_cons_columns col
                           ON ( c.constraint_name = col.constraint_name AND c.owner = col.owner) 
                  WHERE c2.constraint_type = 'R'
                    AND c.table_name = 'MY_TABLE'
                    AND c.owner = 'ALEK'
                    AND col.column_name = 'CODE'
               )
      loop
          dbms_output.put_line(s.statement);
          execute immediate s.statement;
      end loop;
  end;
  

  这给出（并执行）：

  alter table YETANOTHERTABLE modify constraint CK2 disable
  alter table ANOTHERTABLE modify constraint CK1 disable
  

  回复于 2024-04-27T23:54:57+08:00
• 2

  尝试该查询以生成所需的所有更改：

  SELECT  'alter table ' || table_name || ' disable constraint ' ||  constraint_name || ';' from (
    select distinct a.table_name, a.constraint_name
    FROM all_cons_columns a
    JOIN all_constraints c ON a.owner = c.owner
                      AND a.constraint_name = c.constraint_name
    JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                         AND c.r_constraint_name = c_pk.constraint_name
    WHERE c.constraint_type = 'R'
    AND c_pk.table_name = 'MY_TABLE');
  

  回复于 2024-04-27T23:54:57+08:00
• 1

  您可以避免PL / SQL代码和几个动态构造的 alter table 语句 . 要禁用依赖于特定表的主键的所有外键，只需使用 cascade 子句禁用主键，然后再次重新启用（如果需要） .

  这是一个例子：

  --drop table t3;
  --drop table t2;
  --drop table t1;
  create table t1(c1 number primary key);
  create table t2(c1 number references t1(c1));
  create table t3(c1 number references t1(c1));
  
  select table_name
       , constraint_type
       , status
    from user_constraints
    where table_name in ('T1','T2', 'T3')
  
  TABLE C STATUS    
  ----- - ----------
  T2    R ENABLED   
  T1    P ENABLED   
  T3    R ENABLED   
  
  3 rows selected.
  

  禁用外键：

  alter table t1 disable primary key cascade;
  alter table t1 enable  primary key;
  

  结果：

  select table_name
       , constraint_type
       , status
    from user_constraints
    where table_name in ('T1','T2', 'T3')
  
  TABLE C STATUS    
  ----- - ----------
  T2    R DISABLED  
  T1    P ENABLED   
  T3    R DISABLED  
  
  3 rows selected.
  

  Note ：必须手动完成's not possible to enable all foreign key constraints again in cascade mode. It' .

  回复于 2024-04-27T23:54:57+08:00