Désactiver toutes les contraintes de table dans Oracle

Question

Comment désactiver toutes les contraintes de table dans Oracle avec une seule commande ? Cela peut être soit pour une seule table, soit pour une liste de tables, soit pour toutes les tables.

Answer 1

Il est préférable d'éviter d'écrire des fichiers spool temporaires. Utilisez un bloc PL/SQL. Vous pouvez l'exécuter depuis SQL*Plus ou le placer dans un package ou une procédure. La jointure à USER_TABLES est là pour éviter les contraintes de vue.

Il est peu probable que vous souhaitiez vraiment désactiver toutes les contraintes (y compris NOT NULL, les clés primaires, etc.). Vous devriez penser à mettre le type de contrainte dans la clause 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'
   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;
/

La réactivation des contraintes est un peu plus délicate : vous devez activer les contraintes de clé primaire avant de pouvoir les référencer dans une contrainte de clé étrangère. Cela peut être fait en utilisant un ORDER BY sur le type de contrainte. 'P' = clé primaire, 'R' = clé étrangère.

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;
/

Answer 2

Prendre en compte les dépendances entre les contraintes :

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;
/

Answer 3

Il ne s'agit pas d'une commande unique, mais voici comment je procède. Le script suivant a été conçu pour être exécuté dans SQL*Plus. Notez, j'ai volontairement écrit ceci pour ne fonctionner que dans le schéma actuel.

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

Pour restreindre ce que vous déposez, filtrez en ajoutant une clause where à l'instruction select:-

• filtre sur le type de contrainte pour ne supprimer que certains types de contraintes.
• filtre sur le nom de la table pour ne le faire que pour une ou quelques tables.

Pour fonctionner sur d'autres schémas que le schéma actuel, modifiez l'instruction select pour sélectionner dans all_constraints plutôt que dans user_constraints.

Note - pour une raison quelconque, je n'arrive pas à faire en sorte que le soulignement n'agisse PAS comme une italique dans le paragraphe précédent. Si quelqu'un sait comment résoudre ce problème, n'hésitez pas à modifier cette réponse.

Answer 4

Utilisez le curseur suivant pour désactiver toutes les contraintes... Et modifier la requête pour activer les contraintes...

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;
/

Answer 5

Cette opération peut être scriptée en PL/SQL assez simplement à partir de la vue système DBA/ALL/USER_CONSTRAINTS, mais divers détails font que cette opération n'est pas aussi triviale qu'il n'y paraît. Vous devez faire attention à l'ordre dans lequel cela est fait et vous devez également tenir compte de la présence d'index uniques.

L'ordre est important car vous ne pouvez pas supprimer une clé unique ou primaire qui est référencée par une clé étrangère, et il peut y avoir des clés étrangères sur des tables dans d'autres schémas qui référencent des clés primaires dans le vôtre, donc à moins que vous ayez le privilège ALTER ANY TABLE, vous ne pouvez pas supprimer ces PKs et UKs. De plus, vous ne pouvez pas transformer un index unique en index non unique, vous devez donc le supprimer pour pouvoir supprimer la contrainte (pour cette raison, il est presque toujours préférable d'implémenter les contraintes uniques comme une "vraie" contrainte supportée par un index non unique).