`ALTER TABLE`

Objectif

Modifie la structure de la table.

Disponible en

DSQL, ESQL.

Syntaxe

ALTER TABLE tablename
  <operation> [, <operation>];

<operation> ::=
    ADD <col_def>
  | ADD <tconstraint>
  | DROP colname
  | DROP CONSTRAINT constr_name
  | DROP SQL SECURITY
  | ALTER [COLUMN] colname <col_mod>
  | ALTER SQL SECURITY {DEFINER | INVOKER}
  | {ENABLE | DISABLE} PUBLICATION

<col_def> ::=
    <regular_col_def>
  | <computed_col_def>
  | <identity_col_def>

<regular_col_def> ::=
  colname { <datatype> | domainname }
  [DEFAULT {literal | NULL | <context_var>}]
  [NOT NULL]
  [<col_constraint>]
  [COLLATE collation_name]


<computed_col_def>  ::=
  colname [<datatype>]
  {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)

<identity_col_def> ::=
  colname [<datatype>] {ALWAYS | GENERATED BY} DEFAULT AS IDENTITY
  [(START WITH startvalue)] [<col_constraint>]

<col_mod> ::=
     TO newname
  |  POSITION newpos
  | <regular_col_mod>
  | <computed_col_mod>
  | <identity_col_mod>

<regular_col_mod> ::=
  |  TYPE { <datatype>  | domain_name }
  |  SET DEFAULT {literal | NULL | <context_var>}
  |  DROP DEFAULT
  |  SET NOT NULL
  |  DROP NOT NULL

<computed_col_mod> ::=
  [TYPE <datatype>] {GENERATED ALWAYS AS | COMPUTED [BY]} (<expression>)

<identity_col_mod> ::=
     <alter identity column option> ...
  |  SET GENERATED { ALWAYS | BY DEFAULT } [<alter identity column option> ...]
  |  DROP INDENITY

<alter identity column option> ::=
     RESTART [ WITH startvalue ]
  |  SET INCREMENT [BY] incrementvalue

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::= Voir Syntaxe des types de données scalaires.

<blob_datatype> ::= Voir Syntaxe du type de données BLOB.

<array_datatype> ::= Voir syntaxe des tableaux.

<col_constraint> ::=
  [CONSTRAINT constr_name]
  {   UNIQUE [<using_index>]
    | PRIMARY KEY [<using_index>]
    | REFERENCES other_table [(other_col)]
        [ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [<using_index>]
    | CHECK (<check_condition>)
  }

<tconstraint> ::=
  [CONSTRAINT constr_name]
  {   UNIQUE (<col_list>) [<using_index>]
    | PRIMARY KEY (<col_list>) [<using_index>]
    | FOREIGN KEY (<col_list>)
      REFERENCES other_table [(<col_list>)]
        [ON DELETE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE { NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [<using_index>]
    | CHECK (<check_condition>)
  }

<col_list> ::= colname [, colname ...]

<using_index> ::= USING [ASC[ENDING] | DESC[ENDING]] INDEX indexname

<check_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT <val>
  | <val> IS [NOT] {TRUE | FALSE | UNKNOWN}
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<check_condition>)
  | NOT <check_condition>
  | <check_condition> OR <check_condition>
  | <check_condition> AND <check_condition>


<operator> ::=
    <> | != | ^= | ~= | = | < | > | <= | >=
  | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    colname [[<array_idx> [, <array_idx> ...]]]
  | literal
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func(<val> [, <val> ...])


<cast_type> ::=
   <datatype>
  | [TYPE OF] domain_name
  | TYPE OF COLUMN rel.colname

Table 1. Paramètres de l’opérateur `ALTER TABLE`
Paramètre	Description
tablename	Nom de la table.
operation	Une des opérations autorisées pour modifier la structure d’une table.
colname	Nom de la colonne de la table, peut contenir jusqu’à 63 caractères.Doit être unique au sein de la table.
newname	Nouveau nom de colonne de la table, peut avoir jusqu’à 63 caractères.Doit être unique au sein de la table.
gencolname	Le nom de la colonne de la table à calculer.
idencolname	Nom de la colonne d’identité.
newpos	Nouvelle position de la colonne dans le tableau.Un nombre entier compris entre 1 et le nombre de colonnes du tableau.
datatype	Type de données SQL.
domain_name	Nom de domaine.
startvalue	La valeur initiale de la colonne d’identité.
incrementvalue	La valeur incrémentale pour la colonne d’identité.Doit être différent de 0.
col_constraint	Restriction de la colonne.
tconstraint	Contrainte de la table.
constr_name	Nom de la contrainte, peut contenir jusqu’à 63 caractères.
other_table	Le nom de la table à laquelle la clé étrangère fait référence.
other_col	La colonne de la table à laquelle la clé étrangère fait référence.
using_index	Vous permet de définir le nom de l’index généré automatiquement pour la contrainte, et éventuellement de définir si l’index sera en ordre croissant (par défaut) ou décroissant.
literal	Litéral.
context_var	Toute variable contextuelle dont le type est compatible avec le type de données de la colonne.
check_condition	Condition de contrôle des contraintes.Exécuté si évalué comme `TRUE` ou NULL/UNKNOWN.
collation_name	Le nom de l’ordre de tri.Doit être spécifié si vous voulez que l’ordre de tri d’une colonne soit différent de l’ordre de tri par défaut pour le jeu de caractères de cette colonne.
select_one	L’opérateur `SELECT` sélectionne une colonne et renvoie une seule ligne.
select_list	L’opérateur `SELECT` sélectionne une colonne et retourne zéro ou plusieurs lignes.
select_expr	L’opérateur `SELECT` sélectionne plusieurs colonnes et renvoie zéro ou plusieurs lignes.
experssion	Expression.
genname	Le nom de la séquence (générateur).
func	Fonction scalaire.

L’instruction ALTER TABLE modifie la structure d’une table existante.Une seule instruction ALTER TABLE permet de multiples opérations d’ajout/suppression de colonnes et de contraintes, ainsi que des modifications de colonnes.La liste des opérations effectuées lors de la modification d’une table est séparée par une virgule.

Compteur de format

Certaines modifications apportées à la structure des tables augmenteront le compteur de format attribué à chaque table.Le nombre de formats pour chaque table est limité à 255.Lorsque le compteur de format atteint cette valeur, vous ne pouvez plus modifier la structure de la table.

Remise à zéro du compteur de format

Pour réinitialiser le compteur de format, la base de données doit être sauvegardée et restaurée (avec l’utilitaire gbak).

docnext count = 20

la commande `ADD`

La phrase ADD vous permet d’ajouter une nouvelle colonne ou une nouvelle contrainte de table.La syntaxe de définition d’une colonne et la syntaxe de description d’une contrainte de table sont exactement les mêmes que la syntaxe décrite dans le document CREATE TABLE.

impact sur le compteur de format :

Chaque fois qu’une nouvelle colonne est ajoutée, le numéro du format est incrémenté de un.
L’ajout d’une nouvelle contrainte de table n’entraîne pas l’augmentation du numéro de format.

Example 1. Ajout d’une colonne à une table

ALTER TABLE COUNTRY
ADD CAPITAL VARCHAR(25);

Example 2. Ajout d’une colonne avec la restriction `NOT NULL'.

ALTER TABLE OBJECTS
ADD QUANTITY INT DEFAULT 1 NOT NULL;

Important

Notez la clause DEFAULT, qui est obligatoire pour ajouter une contrainte NOT NULL s’il y a des données dans la table.Le fait est que dans ce cas, il y a également un contrôle de validité des données.Et comme lorsqu’une nouvelle colonne est ajoutée, elle contient NULL pour toutes les lignes de la table, une exception sera générée.

Example 3. Ajout d’une colonne avec une restriction d’unicité et suppression d’une autre colonne

ALTER TABLE COUNTRY
  ADD CAPITAL VARCHAR(25) UNIQUE,
  DROP CURRENCY;

Pour ajouter des contraintes au niveau de la table, utilisez la phrase ADD [CONSTRAINT].

Ajout d’une contrainte de contrôle et d’une clé étrangère

ALTER TABLE JOB
ADD CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY),
ADD FOREIGN KEY (JOB_COUNTRY)
REFERENCES COUNTRY (COUNTRY);

Warning

Attention, lorsqu’on ajoute une nouvelle contrainte CHECK, il n’y a pas de vérification que les données précédemment saisies correspondent à celle-ci.Nous vous recommandons donc de vérifier les données de la table avant d’ajouter une telle contrainte.

La commande `DROP`

La commande DROP supprime la colonne de la table spécifiée.Une colonne de table ne peut pas être supprimée si des dépendances existent sur elle.En d’autres termes, la colonne ne doit pas être référencée pour pouvoir être supprimée avec succès.Les références à une colonne peuvent être contenues dans :

dans les contraintes de colonnes ou de tables ;
dans les index ;
dans les procédures stockées et les déclencheurs ;
dans les vues.

Chaque fois qu’une colonne est supprimée, le numéro du format est incrémenté de un.

La commande `DROP CONSTRAINT`

La commande DROP CONSTRAINT supprime la contrainte de colonne ou de table spécifiée.Une contrainte de clé primaire ou une contrainte de clé unique ne peut pas être supprimée si elle est utilisée dans une contrainte de clé étrangère d’une autre table.Dans ce cas, la contrainte FOREIGN KEY doit être supprimée avant de supprimer la contrainte PRIMARY KEY ou UNIQUE à laquelle elle fait référence.

La suppression d’une contrainte de colonne ou de table n’augmente pas le numéro de format.

La commande `DROP SQL SECURITY`

La commande DROP SQL SECURITY supprime les privilèges d’exécution pour la table.Après la suppression des privilèges d’exécution, les colonnes calculées de la table seront calculées avec les privilèges de l’appelant.Les déclencheurs s’exécuteront également avec les privilèges de l’appelant, sauf si leurs privilèges d’exécution sont explicitement remplacés dans le déclencheur.

La commande `ALTER [COLUMN]`

La commande ALTER [COLUMN] vous permet de modifier les caractéristiques suivantes des colonnes existantes :

changer le nom (ne change pas le numéro du format) ;
changer le type de données (augmente le numéro de format d’une unité) ;
changer la position de la colonne dans la liste des colonnes du tableau (ne change pas le numéro du format) ;
suppression de la valeur par défaut d’une colonne (ne change pas le numéro de format) ;
ajouter une valeur par défaut à une colonne (ne change pas le numéro de format) ;
changer le type et l’expression d’une colonne calculée (ne change pas le numéro de format) ;
ajout d’une contrainte NOT NULL (ne change pas le numéro de format) ;
Suppression de la contrainte NOT NULL (ne change pas le numéro de format).

Renommer la colonne

Le mot-clé TO renomme une colonne existante.Le nouveau nom de colonne ne doit pas être présent dans la table.

Le nom d’une colonne ne peut pas être modifié si cette colonne est incluse dans une contrainte — clé primaire ou unique, clé étrangère, contrainte de colonne ou contrainte de contrôle de table CHECK.Le nom de la colonne ne peut pas non plus être modifié si cette colonne de la table est utilisée dans un déclencheur, une procédure stockée ou une vue.

Example 1. Renommer une colonne de table

ALTER TABLE STOCK
ALTER COLUMN MODELNAME TO NAME;

Changer le type de colonne

Le mot-clé TYPE change le type d’une colonne existante en un autre type valide.Tout type de modification qui entraînerait une perte de données n’est pas autorisé.Par exemple, le nombre de caractères du nouveau type de colonne ne peut pas être inférieur à celui qui a été défini précédemment.

Si une colonne a été déclarée comme un tableau, ni son type ni sa dimension ne peuvent être modifiés.

Le type de données d’une colonne faisant partie d’une correspondance clé étrangère / clé primaire (unique) ne peut pas être modifié.

Example 1. Modification du type de colonne d’une table

ALTER TABLE STOCK
ALTER COLUMN ITEMID TYPE BIGINT;

Modification de la position de la colonne

Le mot-clé POSITION permet de modifier la position d’une colonne existante.Les positions des colonnes sont numérotées à partir de 1.

Si un numéro de position inférieur à 1 est spécifié, un message d’erreur approprié sera émis.
Si un numéro de position supérieur au nombre de colonnes du tableau est saisi, les modifications ne seront pas exécutées, mais aucun message d’erreur ou d’avertissement ne suivra.

Example 1. Changer la position d’une colonne de tableau

ALTER TABLE STOCK
ALTER COLUMN ITEMID POSITION 5;

Définition et suppression de la valeur par défaut

La commande DROP DEFAULT supprime la valeur par défaut d’une colonne de table.

Si la colonne est basée sur un domaine avec une valeur par défaut — la valeur du domaine annulera cette suppression.
Si la valeur par défaut est supprimée sur une colonne qui n’a pas de valeur par défaut, ou dont la valeur par défaut est basée sur le domaine, cela fera échouer cette instruction.

Example 1. Suppression de la valeur par défaut d’une colonne

ALTER TABLE STOCK
ALTER COLUMN MODEL DROP DEFAULT;

La commande SET DEFAULT définit la valeur par défaut de la colonne de la table.Si la colonne avait déjà une valeur par défaut, elle sera remplacée par la nouvelle valeur.La valeur par défaut d’une colonne a toujours priorité sur la valeur par défaut du domaine.

Example 2. Définir une valeur par défaut pour la colonne

ALTER TABLE STOCK
ALTER COLUMN MODEL SET DEFAULT 1;

Définition et suppression d’une restriction NOT NULL

La commande SET NOT NULL ajoute une contrainte NOT NULL à la colonne de la table.

Note

L’ajout réussi d’une contrainte NOT NULL ne se produit qu’après une vérification de la table complète pour s’assurer que la colonne ne contient aucune valeur NULL.

Une contrainte explicite `NOT NULL' sur une colonne basée sur le domaine remplace les paramètres du domaine.Dans ce cas, la modification du domaine pour autoriser une valeur NULL ne s’applique pas à la colonne de la table.

Example 1. Ajout d’une restriction `NOT NULL'.

ALTER TABLE STOCK
ALTER COLUMN PROPID SET NOT NULL;

La commande DROP NOT NULL supprime la contrainte NOT NULL pour une colonne de table.Si la colonne est basée sur un domaine avec une contrainte NOT NULL, la contrainte du domaine prévaudra sur cette suppression.

Example 2. Suppression de la restriction `NOT NULL'.

ALTER TABLE STOCK
ALTER COLUMN ITEMID DROP NOT NULL;

Modification des colonnes d’identité

Pour les colonnes d’identité, il est possible de modifier la méthode de génération, la valeur de départ et la valeur d’incrémentation.

La commande SET GENERATED vous permet de changer la façon dont la colonne d’identité est générée.Il existe deux façons de générer une colonne d’identité :

Les colonnes BY DEFAULT permettent de remplacer la valeur générée par le système dans les opérateurs INSERT, UPDATE OR INSERT, MERGE en spécifiant simplement la valeur de cette colonne dans la liste des valeurs.
Les colonnes ALWAYS ne permettent pas d’écraser une valeur générée par le système, une erreur sera générée lors de la tentative d’écraser la valeur d’une telle colonne d’identité. Il est seulement possible d’écraser la valeur de cette colonne dans une instruction INSERT en spécifiant la directive OVERRIDING SYSTEM VALUE.

Example 1. Modification de la manière dont la colonne d’identité est générée

ALTER TABLE objects
ALTER ID SET GENERATED ALWAYS;

Si seule la clause RESTART est spécifiée, la valeur du générateur est remise à zéro.La clause optionnelle WITH permet de spécifier une valeur autre que zéro pour la nouvelle valeur de l’oscillateur interne.

Example 2. Modifier la valeur du générateur actuel pour la colonne d’identité

ALTER TABLE objects
ALTER ID RESTART WITH 100;

La commande SET INCREMENT [BY] vous permet de changer la valeur d’incrémentation de la colonne d’identité.La valeur de l’incrément doit être différente de 0.

Example 3. Modification de l’incrément de la colonne d’identité

ALTER TABLE objects
ALTER ID SET INCREMENT BY 2;

Plusieurs propriétés d’une colonne d’identité peuvent être modifiées dans une seule déclaration, par exemple

Example 4. Modification de plusieurs propriétés d’une colonne d’identité

ALTER TABLE objects
ALTER ID SET GENERATED ALWAYS RESTART SET INCREMENT BY 2;

La commande DROP IDENTITY supprime la séquence système associée à la colonne identité et la convertit en une colonne normale.

Example 5. Transformer une colonne d’identité en colonne régulière

ALTER TABLE objects
ALTER ID DROP INDENTITY;

Modification des colonnes calculées

Pour les colonnes calculées (GENERATED ALWAYS AS ou COMPUTED BY), il est possible de modifier le type et l’expression de la colonne calculée.Il n’est pas possible de transformer une colonne normale en une colonne calculée et vice versa.

Example 1. Modification des colonnes calculées

ALTER TABLE SALARY_HISTORY
ALTER NEW_SALARY GENERATED ALWAYS
AS (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100),
ALTER SALARY_CHANGE COMPUTED
BY (OLD_SALARY * PERCENT_CHANGE / 100);

Attributs qui ne sont pas modifiables

Pour l’instant, il n’est pas possible de modifier l’option de tri par défaut..

La commande `ALTER SQL SECURITY`

La commande ALTER SQL SECURITY permet de modifier les privilèges avec lesquels les colonnes calculées sont calculées.Si l’option INVOKER est sélectionnée, les colonnes calculées sont calculées avec les privilèges de l’utilisateur appelant.Si l’option DEFINER est sélectionnée, les colonnes calculées sont calculées avec les privilèges de l’utilisateur définissant (propriétaire). Par défaut, les colonnes calculées sont calculées avec les privilèges de l’utilisateur appelant.Les triggers héritent également des privilèges d’exécution de la table, s’ils ne sont pas remplacés par les triggers eux-mêmes.

ALTER TABLE COUNTRY
ALTER SQL SECURITY DEFINER;

Gestion de la réplication

La commande ENABLE PUBLICATION inclut la table dans le jeu de réplication (publication). De même, la proposition DISABLE PUBLICATION exclut la table du jeu de réplication.

Example 1. Ajout d’une table à un ensemble de réplication

ALTER TABLE COUNTRY
ENABLE PUBLICATION;

Qui peut changer la table ?

L’instruction ALTER TABLE peut être exécutée :

Administrateurs
Le propriétaire de la table ;
Utilisateurs avec le privilège ALTER ANY TABLE.

Voir aussi :

CREATE TABLE, RECREATE TABLE.

`DROP TABLE`

Destination

Supprime une table existante.

Disponible en

DSQL, ESQL.

Syntaxe

DROP TABLE tablename

Table 1. Paramètres de l’opérateur `DROP TABLE`
Paramètre	Description
tablename	Nom de la table.

L’instruction DROP TABLE supprime une table existante.Si la table a des dépendances, elle ne sera pas supprimée.La suppression d’une table supprime également tous les déclencheurs sur ses événements et les index construits pour ses champs.

Example 1. Suppression d’une table

DROP TABLE COUNTRY;

Qui peut supprimer la table ?

L’instruction DROP TABLE peut être exécutée :

Administrateurs
Le propriétaire de la table ;
Utilisateurs ayant le privilège `DROP ANY TABLE'.

Voir aussi :

CREATE TABLE, RECREATE TABLE.

`RECREATE TABLE`

Objectif

Pour créer une nouvelle table ou recréer une table existante.

Disponible en

DSQL.

Syntaxe

RECREATE [GLOBAL TEMPORARY] TABLE tablename
  [EXTERNAL [FILE] 'filespec']
  (<col_def> [, <col_def> | <tconstraint> ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]
  [SQL SECURITY {DEFINER | INVOKER}]

Pour une description complète des définitions de colonne et des contraintes de table, voir CREATE TABLE.

Crée ou recrée une table.Si une table portant ce nom existe déjà, l’instruction RECREATE TABLE tentera de la supprimer et d’en créer une nouvelle.L’instruction RECREATE TABLE ne s’exécutera pas si la table existante a des dépendances.

Exemples

Example 1. Créer ou recréer une table

RECREATE TABLE COUNTRY (
  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL);

Voir aussi :

CREATE TABLE, DROP TABLE.

ALTER TABLE

ALTER TABLE

Compteur de format

docnext count = 20

la commande ADD

La commande DROP

La commande DROP CONSTRAINT

La commande DROP SQL SECURITY

La commande ALTER [COLUMN]

Renommer la colonne

Changer le type de colonne

Modification de la position de la colonne

Définition et suppression de la valeur par défaut

Définition et suppression d’une restriction NOT NULL

Modification des colonnes d’identité

Modification des colonnes calculées

Attributs qui ne sont pas modifiables

La commande ALTER SQL SECURITY

Gestion de la réplication

Qui peut changer la table ?

DROP TABLE

Qui peut supprimer la table ?

RECREATE TABLE

Exemples

`ALTER TABLE`

la commande `ADD`

La commande `DROP`

La commande `DROP CONSTRAINT`

La commande `DROP SQL SECURITY`

La commande `ALTER [COLUMN]`

La commande `ALTER SQL SECURITY`

`DROP TABLE`

`RECREATE TABLE`