vendredi 13 avril 2012

ORA-02266 lors d'un TRUNCATE TABLE

Si lors d'un TRUNCATE d'une table vous obtenez une erreur ORA-02266 c'est que la clé primaire de cette table est référencée par une autre table (ou plusieurs) via une clé étrangère. Cela arrive souvent et c'est ce qui m'est arrivé ce matin:
SQL> truncate table mdw_trace;
truncate table mdw_trace
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

La solution est simple et consiste à retrouver les tables enfants de la table que je veux truncater puis à désactiver les clés étrangères correspondantes.
Pour ce faire j'ai dans ma boite à scripts le script suivant que j'ai appelé find_child_table.sql:
-- find child tables when ORA-02292: integrity constraint
select constraint_name,table_name from user_constraints where r_constraint_name in(
select constraint_name from user_constraints where table_name = upper('&table_name')) ;

Selon la valeur de la variable TABLE_NAME cette requête retourne le nom de la table enfant ainsi que le nom de la Foreign key:
SQL> @find_child_table
SQL> -- find child tables when ORA-02292: integrity constraint
SQL> select constraint_name,table_name from user_constraints where r_constraint_name in(
  2  select constraint_name from user_constraints where table_name = upper('&table_name')) ;
Enter value for table_name: MDW_TRACE
old   2: select constraint_name from user_constraints where table_name = upper('&table_name'))
new   2: select constraint_name from user_constraints where table_name = upper('MDW_TRACE'))

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
MDW_TRACE_PARAMETER_FK_TRACE   MDW_TRACE_PARAMETER

1 row selected.

Dans mon cas la table référençant ma table à tuncater se nomme MDW_TRACE_PARAMETER et la clé étrangère MDW_TRACE_PARAMETER_FK_TRACE. Je peux donc maintenant désactiver cette contrainte pour effectuer mon TRUNCATE:
SQL> alter table MDW_TRACE_PARAMETER disable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.

SQL> truncate table mdw_trace;

Table truncated.

J'ai pu truncater ma table.
Il ne me reste enfin plus qu'à réactiver la contrainte:
SQL> alter table MDW_TRACE_PARAMETER enable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.

7 commentaires:

  1. merci beaucoup çà m'a sauvé :)

    RépondreSupprimer
  2. Mais Ahmed, celà suppose que les données de la table fille ne font pas de références à la clé de la table mère vidée, non?

    RépondreSupprimer
    Réponses
    1. Dans mon cas la table enfant était déjà vide (j'aurais dû le préciser).
      Mais même vide le truncate de la table mère est inhibé par la foreign key. Donc même si a table fille est vide il faut désactiver la FK pour pouvoir efefctuer le truncate. Bien sûr quand tu réactives la FK il ne faut pas qu'il y'ait des lignes dans la table enfant faisant référence à la table mère vidée sinon t'obtiens l'erreur suivante:
      ORA-02298: cannot validate (MDW_TRACE_PARAMETER.MDW_TRACE_PARAMETER_FK_TRACE) - parent keys not found

      Supprimer
  3. Top. Clair et bien utile. Merci

    RépondreSupprimer
  4. Pour générer un script qui peut être utile en cas de contraintes multiples, et à condition de disposer des droits (mais vous devez les avoir si vous avez lancé ce qui précède)

    ==> Recherche la clef primaire de la table passée en paramètre et génération du script pour les désactiver. Même chose pour l'enable.

    select 'ALTER TABLE '||TABLE_NAME || ' DISABLE CONSTRAINT ' ||CONSTRAINT_NAME || ' ;' from all_constraints where r_constraint_name = (
    select constraint_name from user_constraints where table_name = upper('&table_name') and CONSTRAINT_TYPE= 'P');

    RépondreSupprimer
  5. Merci beaucoup.
    Très pratique et bien expliqué.

    RépondreSupprimer