Overblog Suivre ce blog
Editer l'article Administration Créer mon blog
17 mars 2012 6 17 /03 /mars /2012 13:21

 

SQL TRUNCATE TABLE ORACLE.

Comment supprimer et vider une table Oracle avec TRUNCATE TABLE. ?
Comment gérer l'erreur ORA-02266: les clés primaires/uniques de la table sont référencées par des clés étrangères. ?
Comment supprimer la clé primaire de la table parent. ?
Comment désactiver la clé étrangère de la table enfant. ?

TRUNCATE TABLE.

L'instruction DDL TRUNCATE TABLE supprime tous les enregistrements d’une table et peut libérer l’espace de stockage.
Le problème majeur survenant lors d'un Truncate table provient souvent de la contrainte référentielle.
Celle ci concerne toujours deux tables, une table parent et une table enfant possédant une colonne en commun. Pour la table parent, c'est une clé primaire et pour la table enfant, c'est une clé étrangère (FK).

Dans l'exemple ci-dessous nous allons essayer de tronquer une table parent (TABLE_A) qui a sa colonne COL_1 (PK) référencée par la colonne COL_2 (FK) dans une table enfant (TABLE_B).

Attention, pas de rollback posible avec la commande Oracle Truncate Table.

Syntaxe TRUNCATE TABLE.

 
 TRUNCATE { TABLE [ schema. ]table [ { PRESERVE | PURGE } MATERIALIZED VIEW LOG ] | CLUSTER [ schema. ]cluster } [ { DROP | REUSE } STORAGE ] ; 

sql-truncate-table

 

CREATION ENVIRONNEMENT TRUNCATE DE TEST (tables Oracle).

 
 C:\>SET ORACLE_SID=DBTEST C:\>SQLPLUS scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mer. Ao¹t 3 14:29:49 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connecté à : Oracle Database 10g Release 10.2.0.1.0 - Production SQL> CREATE TABLE SCOTT.TABLE_A ( COL_1 VARCHAR2(30 BYTE) NULL, COL_2 INTEGER NOT NULL, COL_3 VARCHAR2(50 BYTE) NOT NULL, CONSTRAINT TABLE_A_PK PRIMARY KEY (COL_1) USING INDEX TABLESPACE USERS ) TABLESPACE USERS; Table créée. SQL> SQL> CREATE TABLE SCOTT.TABLE_B ( COL_1 VARCHAR2(10 BYTE) NOT NULL, COL_2 VARCHAR2(30 BYTE) NOT NULL, CONSTRAINT TABLE_B_R01 FOREIGN KEY (COL_2) REFERENCES SCOTT.TABLE_A (COL_1) ) TABLESPACE USERS; Table créée. SQL> Insert into SCOTT.TABLE_A Values('a', 1, 'aa'); Insert into SCOTT.TABLE_A Values('b', 1, 'bb'); Insert into SCOTT.TABLE_A Values('c', 1, 'cc'); Insert into SCOTT.TABLE_B Values('aaa', 'a'); Insert into SCOTT.TABLE_B Values('bbb', 'b'); Insert into SCOTT.TABLE_B Values('ccc', 'c'); Insert into SCOTT.TABLE_B Values('aab', 'a'); 7 ligne créées. SQL> COMMIT; Validation effectuée. 

 

Tentative de suppression direct avec TRUNCATE TABLE.

Ici, nous savons déjà que cela va se traduire par un échec avec un ORA-02266.

 
 SQL> TRUNCATE TABLE SCOTT.TABLE_A; TRUNCATE TABLE SCOTT.TABLE_A * ERREUR à la ligne 1 : ORA-02266: les clés primaires/uniques de la table sont référencées par des clés étrangères SQL> 

on ne peut pas supprimer un enregistrement de la table parent si un enregistrement enfant y est rattaché.

Deux méthodes pour éviter l'erreur ORA-02266 avec la commande TRUNCATE ci-dessous.

 

DROP PRIMARY KEY table A puis TRUNCATE TABLE.

 
 SQL> ALTER TABLE SCOTT.TABLE_A DROP PRIMARY KEY CASCADE; Table modifiée. SQL> SQL> TRUNCATE TABLE SCOTT.TABLE_A; Table tronquée. SQL> 

 

DISABLE CONSTRAINT table B puis TRUNCATE TABLE et enfin RE-ENABLE CONSTRAINT.

 
 SQL> ALTER TABLE SCOTT.TABLE_B DISABLE CONSTRAINT "TABLE_B_R01"; Table modifiée. SQL> SQL> TRUNCATE TABLE SCOTT.TABLE_A; Table tronquée. SQL> ALTER TABLE SCOTT.TABLE_B MODIFY CONSTRAINT TABLE_B_R01 ENABLE NOVALIDATE; Table modifiée. SQL> 

 

SUPPRESSION ENVIRONNEMENT DE TEST (tables Oracle).

 
 SQL> DROP TABLE SCOTT.TABLE_A CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP TABLE SCOTT.TABLE_B CASCADE CONSTRAINTS PURGE; Table supprimée. 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• SQL INSERT INTO TABLE ORACLE
• CREATE TABLE ORACLE
• Désactiver toutes les FOREIGN KEY FK référençant une table.
• Lister les CONTRAINTES d'une Table ou View.
• Lister et désactiver les CHECK CONSTRAINTS d'une Table.

Partager cet article

Repost 0
Published by Daniel R. - dans COMMANDES SQL DDL-LDD
commenter cet article

commentaires