Contraintes integrite

Fiche express
Type	Technique SQL/PL-SQL générique
Rôle	Activer/désactiver en masse les contraintes d'intégrité d'un schéma
Voir aussi	Sql · Drop db link

Ces blocs PL/SQL permettent d'activer ou de désactiver en masse toutes les contraintes d'intégrité (clés primaires, contraintes référentielles, contraintes de vérification) d'un schéma Oracle — utile par exemple avant un chargement massif de données ou une restauration partielle, où les contraintes gêneraient l'ordre d'insertion.

Désactiver toutes les contraintes actives d'un schéma

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

Réactiver toutes les contraintes désactivées

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

Note : ces deux blocs s'appuient sur user_constraints/user_tables, donc sur le schéma de la session courante. Exécutables directement dans une session SQL*Plus — préfixer par exec n'a de sens que pour appeler une procédure déjà compilée, pas un bloc anonyme comme ceux-ci.

Version procédure stockée (paramétrable par schéma)

Variante en procédure PL/SQL réutilisable, prenant le owner du schéma et le sens (activation/désactivation) en paramètres :

/**********************************************************************
* Nom : DatabaseContrainte
* Description : Active ou désactive les contraintes d'un schéma
* (IN) pi_owner : nom du owner du schéma
* (IN) pi_mode  : 1 = active les contraintes, sinon désactive
**********************************************************************/
PROCEDURE DatabaseContrainte(pi_owner IN VARCHAR2, pi_mode IN NUMBER) IS

  CURSOR c_contrainte(pi_owner VARCHAR2) IS
    SELECT fk.owner, fk.constraint_name, fk.table_name,
           decode(fk.constraint_type, 'P', 1, 'R', 2, 3) AS colonne
    FROM all_constraints fk
    WHERE fk.owner = pi_owner
    AND fk.constraint_type IN ('R', 'P', 'C')
    ORDER BY colonne;

  v_table       all_constraints.table_name%TYPE;
  v_owner       all_constraints.owner%TYPE;
  v_contrainte  all_constraints.constraint_name%TYPE;
  v_colonne     NUMBER(1);

BEGIN
  OPEN c_contrainte(pi_owner);
  LOOP
    FETCH c_contrainte INTO v_owner, v_contrainte, v_table, v_colonne;
    EXIT WHEN c_contrainte%NOTFOUND;

    IF (pi_mode = 1) THEN
      EXECUTE IMMEDIATE 'ALTER TABLE ' || v_owner || '.' || v_table ||
        ' ENABLE CONSTRAINT ' || v_contrainte;
    ELSE
      EXECUTE IMMEDIATE 'ALTER TABLE ' || v_owner || '.' || v_table ||
        ' DISABLE CONSTRAINT ' || v_contrainte || ' CASCADE';
    END IF;
  END LOOP;
  CLOSE c_contrainte;
END DatabaseContrainte;

Appel (exemple générique) :

exec DatabaseContrainte(pi_owner => 'APP_SCHEMA', pi_mode => 1)

Vérification de l'état des contraintes après coup :

SELECT constraint_name, status FROM dba_constraints WHERE owner = 'APP_SCHEMA';

Lister les contraintes d'une table

SELECT owner, constraint_name,
       decode(constraint_type, 'C', 'check', 'P', 'clé primaire',
              'U', 'contrainte dunicité', 'R', 'contrainte référentielle') constraint_type,
       table_name, search_condition, status
FROM user_constraints
WHERE table_name LIKE '%&table_name%'
AND owner LIKE '%&owner%';

(&table_name et &owner sont des variables de substitution SQL*Plus — la requête invite à saisir des motifs de recherche au moment de l'exécution.)

Voir aussi

• Drop db link — autre opération DDL courante en PL/SQL
• Sql — panorama général SQL