代码之家  ›  专栏  ›  技术社区  ›  oneself

禁用Oracle中的所有表约束

  •  81
  • oneself  · 技术社区  · 16 年前

    11 回复  |  直到 16 年前
        1
  •  152
  •   WW.    9 年前

    您不太可能真的想要禁用所有约束(包括NOTNULL、主键等)。您应该考虑将约束类型放入WHERE子句中。

    BEGIN
      FOR c IN
      (SELECT c.owner, c.table_name, c.constraint_name
       FROM user_constraints c, user_tables t
       WHERE c.table_name = t.table_name
       AND c.status = 'ENABLED'
       AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
       ORDER BY c.constraint_type DESC)
      LOOP
        dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
      END LOOP;
    END;
    /
    

    再次启用约束有点棘手-在外键约束中引用它们之前,需要启用主键约束。这可以使用约束类型上的ORDER BY来完成。”P'=主键,R'=外键。

    BEGIN
      FOR c IN
      (SELECT c.owner, c.table_name, c.constraint_name
       FROM user_constraints c, user_tables t
       WHERE c.table_name = t.table_name
       AND c.status = 'DISABLED'
       ORDER BY c.constraint_type)
      LOOP
        dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
      END LOOP;
    END;
    /
    
        2
  •  12
  •   Gilles Quénot ticktalk    13 年前

    要计算约束之间的依赖关系,请执行以下操作:

    SET Serveroutput ON
    BEGIN
        FOR c IN
        (SELECT c.owner,c.table_name,c.constraint_name
        FROM user_constraints c,user_tables t
        WHERE c.table_name=t.table_name
        AND c.status='ENABLED'
        ORDER BY c.constraint_type DESC,c.last_change DESC
        )
        LOOP
            FOR D IN
            (SELECT P.Table_Name Parent_Table,C1.Table_Name Child_Table,C1.Owner,P.Constraint_Name Parent_Constraint,
                c1.constraint_name Child_Constraint
            FROM user_constraints p
            JOIN user_constraints c1 ON(p.constraint_name=c1.r_constraint_name)
            WHERE(p.constraint_type='P'
            OR p.constraint_type='U')
            AND c1.constraint_type='R'
            AND p.table_name=UPPER(c.table_name)
            )
            LOOP
                dbms_output.put_line('. Disable the constraint ' || d.Child_Constraint ||' (on table '||d.owner || '.' ||
                d.Child_Table || ')') ;
                dbms_utility.exec_ddl_statement('alter table ' || d.owner || '.' ||d.Child_Table || ' disable constraint ' ||
                d.Child_Constraint) ;
            END LOOP;
        END LOOP;
    END;
    /
    
        3
  •  5
  •   Dan    12 年前

    set heading off
    
    spool drop_constraints.out
    
    select
        'alter table ' || 
        owner || '.' || 
        table_name || 
        ' disable constraint ' || -- or 'drop' if you want to permanently remove
        constraint_name || ';'
    from
        user_constraints;
    
    spool off
    
    set heading on
    
    @drop_constraints.out
    

    要限制删除的内容,请在select语句中添加where子句:-

    • 过滤约束类型以仅删除特定类型的约束

    若要在多个当前架构上运行,请修改select语句以从所有_约束中选择,而不是从用户_约束中选择。

    -由于某些原因,我无法使下划线在上一段中的行为不象斜体。如果有人知道如何修复它,请随时编辑此答案。

        4
  •  5
  •   sehe    12 年前

    使用以下光标禁用所有约束。。和alter查询以启用约束。。。

    DECLARE
    
    cursor r1 is select * from user_constraints;
    cursor r2 is select * from user_tables;
    
    BEGIN
      FOR c1 IN r1
      loop
        for c2 in r2
        loop
           if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
            dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
           end if;
        end loop;
      END LOOP;
    END;
    /
    
        5
  •  4
  •   David Aldridge    16 年前

        6
  •  1
  •   Cristian    7 年前
    SELECT 'ALTER TABLE '||substr(c.table_name,1,35)|| 
    ' DISABLE CONSTRAINT '||constraint_name||' ;' 
    FROM user_constraints c, user_tables u 
    WHERE c.table_name = u.table_name; 
    

    此语句返回关闭所有约束(包括主键、外键和其他约束)的命令。

        7
  •  0
  •   Adam Bellaire    16 年前

    看起来你不可能用一个命令就能做到这一点,但是 here' 这是我能找到的最接近它的东西。

        8
  •  0
  •   Twinkles    8 年前

    这是禁用约束的另一种方法(它来自 https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817 )

    WITH qry0 AS
           (SELECT    'ALTER TABLE '
                   || child_tname
                   || ' DISABLE CONSTRAINT '
                   || child_cons_name
                     disable_fk
                  ,   'ALTER TABLE '
                   || parent_tname
                   || ' DISABLE CONSTRAINT '
                   || parent.parent_cons_name
                     disable_pk
              FROM (SELECT a.table_name child_tname
                          ,a.constraint_name child_cons_name
                          ,b.r_constraint_name parent_cons_name
                          ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) child_columns
                      FROM user_cons_columns a
                          ,user_constraints b
                     WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R'
                    GROUP BY a.table_name, a.constraint_name
                            ,b.r_constraint_name) child
                  ,(SELECT a.constraint_name parent_cons_name
                          ,a.table_name parent_tname
                          ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) parent_columns
                      FROM user_cons_columns a
                          ,user_constraints b
                     WHERE a.constraint_name = b.constraint_name AND b.constraint_type IN ('P', 'U')
                    GROUP BY a.table_name, a.constraint_name) parent
             WHERE child.parent_cons_name = parent.parent_cons_name
               AND (parent.parent_tname LIKE 'V2_%' OR child.child_tname LIKE 'V2_%'))
    SELECT DISTINCT disable_pk
      FROM qry0
    UNION
    SELECT DISTINCT disable_fk
      FROM qry0;
    

        9
  •  0
  •   liamgriffiths    7 年前

    在“disable”脚本中,order by子句应为:

    ORDER BY c.constraint_type DESC, c.last_change DESC
    

        10
  •  0
  •   diaphol    6 年前

    declare
        constr all_constraints.constraint_name%TYPE;
    begin
        for constr in
            (select constraint_name from all_constraints
            where table_name = 'D'
            and owner = 'TRANEE')
        loop
            execute immediate 'alter table D disable constraint '||constr.constraint_name;
        end loop;
    end;
    /
    

    (如果将“禁用”更改为“启用”,则可以启用所有约束)

        11
  •  0
  •   Cristina Bazar    6 年前

    选择“更改表格”| | substr(c.表格名称,1,35)|| “禁用约束”| |约束|名称| |” --其中c.table_name='table_name';