Plan du site  
français  English
pixel
pixel

Articles - Étudiants SUPINFO

Chapter 09 - Managing Schema Objects

Modifier une table

Après avoir créé une table, vous pouvez modifier sa structure. Par exemple pour ajouter une colonne, pour modifier la définition d’une colonne ou pour supprimer une colonne. Vous pouvez le faire en utilisant la requête ALTER TABLE.

Syntaxe :

ALTER TABLE table
ADD (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
MODIFY (column datatype [DEFAULT expr]
[, column datatype]...);
ALTER TABLE table
DROP (column);

Dans la syntaxe :

  • table est le nom de la colonne

  • ADD|MODIFY|DROP est le type de modification

  • column est le nom de la nouvelle colonne

  • datatype est le type de données de la nouvelle colonne

  • DEFAULT expr spécifie la valeur par défaut de la nouvelle colonne

Ajouter une colonne

Utilisez la clause ADD pour ajouter une colonne.

L’exemple suivant ajoute une colonne nommée JOB_ID dans la table DEPT80.

ALTER TABLE dept80
ADD (job_id VARCHAR2(9));
Table altered.

La colonne JOB_ID devient la dernière colonne dans la table.

Règles :

  • Vous pouvez ajouter ou modifier des colonnes.

  • Vous ne pouvez spécifier où la colonne doit se placer. La nouvelle colonne devient la dernière colonne.

[Note]

Si une table contient déjà des enregistrements quand une colonne est ajoutée, toutes les lignes de la nouvelle colonne sont à NULL. Vous ne pouvez pas ajouter une colonne NOT NULL à une table si les autres colonnes contiennent des données. Vous pouvez ajouter une colonne NOT NULL seulement à une table vide.

Modifier une colonne

Vous pouvez modifier la définition d’une colonne en utilisant la requête ALTER TABLE avec la clause MODIFY. Les modifications de colonnes peuvent inclure des changements de type de données, de taille et de valeur par défaut.

Règles :

  • Vous pouvez augmenter la longueur ou la précision des colonnes numériques.

  • Vous pouvez augmenter la longueur des colonnes numériques ou de chaînes.

  • Vous pouvez diminuer la longueur de la colonne si :

    • La colonne contient seulement des valeurs NULL

    • La table n’a pas d’enregistrements

    • La nouvelle longueur de la colonne n’est pas plus petite que la taille des valeurs présentes dans la colonne

  • Vous pouvez changer le type de données d’une colonne si celle-ci ne contient que des valeurs NULL. Or, il est possible de convertir une colonne de type CHAR en VARCHAR2 même si elle contient des données.

  • Vous pouvez convertir une colonne de type VARCHAR2 en CHAR ou CHAR en VARCHAR2 seulement si la colonne ne contient que des valeurs NULL ou si vous ne changez pas la longueur.

  • Un changement de la valeur par défaut n’affecte que les insertions futures.

Dans l’exemple suivant, la longueur de la colonne LAST_NAME de la table DEPT80 est modifiée.

ALTER TABLE dept80
MODIFY (last_name VARCHAR2(30));
Table altered.

Supprimer une colonne

Vous pouvez supprimer une colonne d’une table en utilisant la requête ALTER TABLE avec la clause DROP COLUMN.

Règles :

  • La colonne peut être vide ou contenir des données.

  • En utilisant la requête ALTER TABLE, une seule colonne peut être supprimée à la fois.

  • Au moins une colonne doit rester dans la table après la modification.

  • Après la suppression d’une colonne, celle-ci ne peut pas être récupérée.

  • Une colonne ne peut pas être supprimée si elle est une partie d’une contrainte ou d’une clé d’index, sans l’utilisation de l’option CASCADE.

  • La suppression d’une colonne peut prendre le temps considérable si la colonne contient une importante quantité de données. Dans ce cas, il est préférable de changer le statut de la colonne en UNUSED puis la supprimer lorsque le nombre d’utilisateurs connectés à la base de données est faible.

L’exemple suivant supprime la colonne JOB_ID de la table DEPT80.

ALTER TABLE dept80
DROP COLUMN job_id;
Table altered.
[Note]

Certaines colonnes ne peuvent jamais être supprimées comme les colonnes qui font partie de la clé partitionnée d’une table partitionnée ou les colonnes qui font partie de la clé primaire d’une table organisée en index.

L'option SET UNUSED

Présentation

L’option SET UNUSED marque une ou plusieurs colonnes comme inutilisées, donc elles peuvent être supprimée quand la demande des ressources système est moindre. Une colonne en état UNUSED n’est pas supprimée physiquement (l’espace disque utilisée par ses données n’est pas libéré). Cependant, le temps de réponse est plus rapide que si vous utilisez la clause DROP. Les colonnes inutilisées sont considérées comme si elles étaient supprimées, même si des données subsistent dans les enregistrements de la table. Après qu’une colonne ait été marquée comme inutilisée, vous n’avez plus aucun accès a cette colonne. Cette action est irréversible.

Syntaxe :

ALTER TABLE <table_name>
SET UNUSED(<column_name>);

ou

ALTER TABLE <table_name>
SET UNUSED COLUMN <column_name>;

L’exemple suivant met la colonne LAST_NAME de la table DEPT80 en état UNUSED.

ALTER TABLE dept80
SET UNUSED (last_name);
Table altered.

Une requête SELECT * ne retrouvera pas les données des colonnes marquées comme UNUSED. De plus, les noms et les types de données des colonnes marquées comme UNUSED ne seront pas affichés lors d’un DESCRIBE. Vous pouvez ajouter une colonne dans la table avec le même nom que la colonne en état UNUSED. Les informations SET UNUSED sont stockées dans la vue USER_UNUSED_COL_TABS du dictionnaire de données.

[Note]

Les règles pour placer une colonne en état UNUSED sont les mêmes que celles pour supprimer une colonne.

L’option DROP UNUSED COLUMNS

L’option DROP UNUSED COLUMNS supprime de la table toutes les colonnes en état UNUSED. Vous pouvez utiliser cette requête quand vous avez besoin d’espace disque occupé par les données des colonnes en état UNUSED. Si la table ne contient aucune colonne en état UNUSED, la requête retourne une erreur.

Syntaxe :

ALTER TABLE <table_name>
DROP UNUSED COLUMNS;

L’exemple suivant supprime les données des colonnes en état UNUSED de la table DEPT80.

ALTER TABLE dept80
DROP UNUSED COLUMNS;
Table altered.

Gestion des contraintes

Ajouter une contrainte

Vous pouvez ajouter une contrainte sur une table existante en utilisant la requête ALTER TABLE avec la clause ADD.

Syntaxe :

ALTER TABLE <table_name>
ADD [CONSTRAINT <constraint_name>]
type (<column_name>);

Dans la syntaxe :

  • table est le nom de la table

  • constraint est le nom de la contrainte

  • type est le type de contrainte

  • column est le nom de la colonne affectée par la contrainte

Le nom de la contrainte est optionnel mais recommandé. Si vous ne nommez pas votre contrainte, le système le fera pour vous.

Règles :

  • Vous pouvez ajouter, supprimer, activer ou désactiver une contrainte, mais vous ne pouvez pas en modifier sa structure

  • Vous pouvez ajouter une contrainte NOT NULL à une colonne existante en utilisant la clause MODIFY de la requête ALTER TABLE

[Note]

Vous pouvez définir la contrainte NOT NULL sur une colonne seulement si la table est vide ou si elle possède des valeurs sur toutes les lignes de la colonne.

Dans l’exemple ci-dessous une contrainte PRIMARY KEY est ajoutée sur la colonne EMPLOYEE_ID de la table EMP2.

ALTER TABLE emp2
MODIFY employee_id PRIMARY KEY;
Table altered.

Remarquez que la contrainte n’est pas nommée, le serveur Oracle génère un nom automatiquement.

Dans l’exemple suivant une contrainte FOREIGN KEY est définie dans la table EMP2. Le deuxième exemple crée une contrainte FOREIGN KEY sur la table EMP2. La contrainte vérifie l’existence du manager comme un employé valide de la table EMP2.

ALTER TABLE emp2
ADD CONSTRAINT emp_mgr_fk
FOREIGN KEY(manager_id)
REFERENCES emp2(employee_id);
Table altered.

ON DELETE CASCADE

L’action ON DELETE CASCADE autorise la suppression (pas la mise à jour) des données de la clé parenté référencée par des entrées de la table enfant. Quand des données de la clé parente sont supprimées, toutes les lignes de la table enfant sont automatiquement effacées. Pour activer cette action il faut inclure l’option ON DELETE CASCADE dans la définition de la contrainte FOREIGN KEY.

ALTER TABLE Emp2 ADD CONSTRAINT emp_dt_fk
FOREIGN KEY (Department_id)
REFERENCES departments ON DELETE CASCADE);
Table altered.

Contraintes différées

Vous pouvez reporter la vérification d’une contrainte jusqu’à la fin de la transaction. Une contrainte différée (DEFERRED) est une contrainte qui est vérifiée uniquement lors du COMMIT. Si une contrainte différée est violée, la transaction est annulée lors d’un COMMIT (il s’agit bien de COMMIT et pas de ROLLBACK car la contrainte est vérifiée à la fin de la transaction).

Si une contrainte est immédiate (IMMEDIATE), la vérification est faite à la fin de chaque requête DML. Si la contrainte est violée, la requête est annulée immédiatement, si une contrainte provoque une action (par exemple DELETE CASCADE), cette action est toujours considérée comme une part de la requête, que la contrainte soit différée ou immédiate.

Utilisez la clause SET CONSTRAINTS pour spécifier, pour une transaction particulière, si une contrainte pouvant être différée (DEFERRABLE) est vérifiée après chaque requête DML ou quand la transaction est validée (COMMIT). Pour créer une contrainte déférée, vous devez créer un index non unique pour cette contrainte.

Vous pouvez définir des contraintes acceptant ou pas la modification du moment de la vérification (DEFERRABLE ou NOT DEFERRABLE). Ces contraintes peuvent être aussi bien initialement différées (INITIALY DEFERRED) ou initialement immédiates (IMMEDIATE). Ces attributs peuvent être différents pour chaque contrainte.

Scénario : Suite au changement de la stratégie de votre entreprise le numéro de département 40 doit être modifié en 45. Changement la colonne DEPARTMENT_ID affecte les employés assignés à ce département. Cependant, vous pouvez rendre la clé primaire et la clé étrangère initialement différées (INITIALLY DEFERRED). Le moment de la vérification pourra être modifié car le mot clé DEFERRABLE est présent. Vous mettez à jours les informations sur les départements et les employés, lors du commit toutes les lignes sont validées.

Différer une contrainte à la création

ALTER TABLE dept2
ADD CONSTRAINT dept2_id_pk
PRIMARY KEY (department_id)
DEFERRABLE INITIALLY DEFERRED

Changer le comportement d’une contrainte spécifique

SET CONSTRAINTS dept2_id_pk IMMEDIATE

Changer le comportement de toutes les contraintes d’une session

ALTER SESSION
SET CONSTRAINTS = IMMEDIATE

Supprimer une contrainte

Pour supprimer une contrainte, vous devez trouver le nom de la contrainte dans les vues du dictionnaire de données USER_CONSTRAINTS et USER_CONS_COLUMNS. Puis utiliser la requête ALTER TABLE avec la clause DROP. L’option CASCADE de la clause DROP supprime toute contrainte dépendante.

Syntaxe :

ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
CONSTRAINT constraint [CASCADE];

Dans la syntaxe :

  • table est le nom de la table

  • column est le nom de la colonne affectée par le contrainte

  • constraint est le nom de la contrainte

Quand vous supprimez une contrainte d’intégrité, la contrainte n’est plus appliquée par le serveur Oracle et n’est plus disponible dans le dictionnaire de données.

Exemples :

Supprimer la contrainte du manager de la table EMP2

ALTER TABLE emp2
DROP CONSTRAINT emp_mgr_fk;
Table altered.

Supprimer la contrainte PRIMARY KEY de la table DEPT2 et supprimer la contrainte associée FOREIGN KEY de la colonne EMP2.DEPARTMENT_ID.

ALTER TABLE dept2
DROP PRIMARY KEY CASCADE;
Table altered.

Activer une contrainte

Désactiver une contrainte

Vous pouvez désactiver une contrainte sans la supprimer ou la recréer mais en vous servant de la requête ALTER TABLE avec la clause DISABLE.

Syntaxe :

ALTER TABLE table
DISABLE CONSTRAINT constraint [CASCADE];

Dans la syntaxe :

  • table est le nom de la table

  • constraint est le nom de la contrainte

Règles :

  • Vous pouvez utiliser la clause DISABLE dans la requête CREATE TABLE aussi bien que la requête ALTER TABLE.

  • La clause CASCADE désactive toutes les contraintes d’intégrité dépendantes.

  • La désactivation d’une clé primaire ou une clé unique entraine la suppression de l’index unique.

Dans l’exemple suivant, la contrainte clé étrangère EMP_DT_FK est désactivée.

ALTER TABLE emp2
DISABLE CONSTRAINT emp_dt_fk;
Table altered.

Activer une contrainte

Vous pouvez activer une contrainte en vous servant de la requête ALTER TABLE avec la clause ENABLE.

Syntaxe :

ALTER TABLE table
ENABLE CONSTRAINT constraint;

Dans la syntaxe :

  • table est le nom de la table

  • constraint est le nom de la contrainte

Règles :

  • Si vous activez une contrainte, cette contrainte s’applique à toutes les données de la table. Toutes les données de la table doivent respecter la contrainte.

  • Si vous activez une contrainte UNIQUE ou PRIMARY KEY , un index unique est créé automatiquement. Si un index existe déjà, il peut être utilisé par ces clés.

  • Vous pouvez utiliser la clause ENABLE dans la requête CREATE TABLE et la requête ALTER TABLE.

  • Activation d’une clé primaire désactivée auparavant avec l’option CASCADE n’active pas les clés étrangères qui sont dépendantes de cette clé primaire.

  • Pour activer une contrainte UNIQUE ou PRIMARY KEY, vous devez avoir les privilèges nécessaires à la création d’un index sur une table.

CASCADE CONSTRAINTS

Ces requêtes illustrent le fonctionnement de la clause CASCADE CONSTRAINTS. La table TEST1 est créée comme suit :

CREATE TABLE test1 (
  pk NUMBER PRIMARY KEY,
  fk NUMBER,
  col1 NUMBER,
  col2 NUMBER,
  CONSTRAINT fk_constraint FOREIGN KEY (fk) REFERENCES test1,
  CONSTRAINT ck1 CHECK (pk > 0 and col1 > 0),
  CONSTRAINT ck2 CHECK (col2 > 0));

Une erreur est retournée pour les requêtes suivantes :

ALTER TABLE test1 DROP (pk); --pk is a parent key.
ALTER TABLE test1 DROP (col1); --col1 is referenced by multicolumn constraint ck1.

La requête suivante supprime la colonne EMPLOYEE_ID, la clé primaire et toutes clés étrangères de la table EMP2 qui référencent la clé primaire :

ALTER TABLE emp2
DROP COLUMN employee_id CASCADE CONSTRAINTS;
Table altered.

Si des colonnes sont supprimées avec toutes les colonnes ayant des contraintes les référençant, la clause CASCADE CONSTRAINTS n’est pas requise. Par exemple, supposant qu’aucune autre contrainte ne fait référence à la colonne PK, il est judicieux d’écrire la requête suivante sans la clause CASCADE CONSTRAINTS:

ALTER TABLE test1
DROP (pk, fk, col1);
Table altered.

Gestion des index

Créer un index

Présentation

Deux types d’index peuvent être créés. Le premier est l’index unique. Le serveur Oracle crée automatiquement un index unique quand vous définissez une colonne ou un groupe de colonnes dans une table avec la contrainte UNIQUE ou la contrainte PRIMARY KEY. Le nom de l’index est le même nom que le nom de la contrainte.

Le deuxième type est l’index non unique qui peut être créé par l’utilisateur. Par exemple, vous pouvez créer un index sur une colonne FOREIGN KEY pour améliorer les performances de requêtes utilisant des jointures.

Vous pouvez créer un index sur une ou plusieurs colonnes en utilisant la requête CREATE INDEX.

[Note]

Vous pouvez créer un index unique manuellement mais il est recommandé de créer une contrainte unique le créant implicitement.

CREATE INDEX dans la requête CREATE TABLE

Dans l’exemple suivant, la clause CREATE INDEX est utilisée dans la requête CREATE TABLE afin de créer un index de clé primaire implicitement. Vous pouvez nommer votre index à la création de la clé primaire pour qu’il soit différent du nom de la contrainte PRIMARY KEY.

CREATE TABLE NEW_EMP
(employee_id NUMBER(6)
  PRIMARY KEY USING INDEX
  (CREATE INDEX emp_id_idx ON
   NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));
Table created.
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'NEW_EMP';

Remarquez que le nom de l’index n’est pas générique, est EMP_ID_IDX comme précisé dans la clause CREATE INDEX de la requête CREATE TABLE. Si vous désactivez la contrainte clé primaire ; l’index créé explicitement ne sera pas supprimé.

Vous pouvez utiliser un index existant pour votre colonne PRIMARY KEY, par exemple, quand vous voulez ajouter une quantité importante de données et vous voulez augmenter la vitesse de l’opération. Vous allez, sans doute, désactiver la contrainte pendant l’exécution et la réactiver après, dans ce cas, un index unique sera toujours présent sur la clé primaire et vérifiera toujours les données pendant le chargement. Donc vous pouvez d’abord créer un index non unique pour la colonne PRIMARY KEY, et ensuite créer la colonne PRIMARY KEY en précisant que vous souhaitez utiliser l’index existant. Les exemples ci-dessus illustrent le déroulement :

Etape 1 : Créez la table

CREATE TABLE NEW_EMP2
 (employee_id NUMBER(6)
 first_name VARCHAR2(20),
 last_name VARCHAR2(25)
 );

Etape 2: Créez l’index

CREATE INDEX emp_id_idx2 ON
new_emp2(employee_id);

Etape 3: Créez la clé primaire

ALTER TABLE new_emp2 
ADD PRIMARY KEY (employee_id) USING INDEX emp_id_idx2;

Index basés sur les fonctions

Les index basés sur les fonctions sont définis avec des fonctions telles qu’UPPER(column_name) ou LOWER(colum_name)permettant une recherche insensible à la casse. Par exemple, l’index suivant :

CREATE INDEX upper_last_name_idx ON emp2 (UPPER(last_name));

améliore les performances des requêtes comme :

SELECT * FROM emp2 WHERE UPPER(last_name) = 'KING';

Le serveur Oracle utilise l’index uniquement lorsque la fonction particulière est utilisée dans la requête. Par exemple, pour la requête suivante Oracle utilisera un index, or si la fonction UPPER(last_name) est pas utilisée dans la clause WHERE le serveur Oracle parcourt la table en entier :

SELECT *
FROM employees
WHERE UPPER (last_name) IS NOT NULL
ORDER BY UPPER (last_name);
[Note]

Le paramètre d’initialisation QUERY_REWRITE_ENABLED doit être initialisé à TRUE pour que les index basés sur les fonctions soient utilisés.

Le serveur Oracle traite les index avec des colonnes marquées DESC comme des index basés sur les fonctions. Les données des colonnes marquées par DESC sont stockées dans l’ordre descendant.

Par exemple :

CREATE INDEX desc_last_name_idx ON emp2 (last_name DESC);

Suppression des index

Vous ne pouvez pas modifier les index. Pour modifier un index, vous devez le supprimer et le recréer. Supprimez un index avec la requête DROP INDEX. Pour supprimer un index vous devez en être le propriétaire ou avoir le privilège DROP ANY INDEX.

Syntaxe :

DROP INDEX index;

Dans la syntaxe :

  • index est le nom de l’index

Exemple :

DROP INDEX upper_dept_name_idx;
Index dropped.
[Note]

Si vous supprimez une table, les index et les contraintes sont automatiquement supprimées, mais les vues et les séquences persistent.

About SUPINFO | Contacts & addresses | Teachers | Press | INVESTOR | Conditions of Use & Copyright | Respect of Privacy
Logo de la société Cisco Logo de la société IBM Logo de la société Sun-Oracle Logo de la société Apple Logo de la société Sybase Logo de la société Novell Logo de la société Intel Logo de la société Accenture Logo de la société SAP Logo de la société Prometric Logo du IT Academy Program par Microsoft

SUPINFO International University is globally operated by EDUCINVEST Belgium - Avenue Louise, 534 - 1050 Brussels
and is accredited in France by Association Ecole Supérieure d'Informatique de Paris (ESI SUPINFO)