FirebirdSQL logo
 DOMAININDEX 

Firebird est une base de données relationnelle. Les données de ces bases de données sont stockées dans des tables. Une table est une structure plate, à deux dimensions, contenant un nombre quelconque de lignes. Les lignes d’une table sont souvent appelées enregistrements. Toutes les lignes d’un tableau ont la même structure et sont composées de colonnes. Les colonnes d’une table sont souvent appelées champs. Une table doit avoir au moins une colonne. Chaque colonne doit être associée à un type de données SQL spécifique.

Cette section explique comment créer, modifier et supprimer des tables dans une base de données.

CREATE TABLE

Destination

Pour créer une nouvelle table.

Disponible en

DSQL, ESQL

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

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

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


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

<identity_col_def> ::=
  colname [<datatype>]
  GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY [(<identity column options>)]
  [<col_constraint>]

<identity column options> ::=
  <identity column option> [<identity column option>]

<identity column option> ::=
    START WITH startvalue
  | 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 <cast_type>)
  | (<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 CREATE TABLE
Paramètre Description

tablename

Nom de la table, peut contenir jusqu’à 63 caractères.

filespec

Spécification du fichier (uniquement pour les tables externes).

colname

Nom de la colonne de la table, peut avoir jusqu’à 63 caractères.

datatype

Type de données SQL.

domain_name

Nom de domaine.

startvalue

La valeur initiale de la colonne d’identité.

identityvalue

Incrément de la colonne d’identité. Ne peut être égal à 0.

col_constraint

Restriction de la colonne.

tconstraint

Limitation du tableau.

constr_name

Nom de la restriction, 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

Literal

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

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 CREATE TABLE crée une nouvelle table. Le nom de la table doit être unique parmi les noms de toutes les tables, vues (VIEW) et procédures de base de données stockées.

Une table peut contenir au moins une colonne et un nombre arbitraire de contraintes de table.

Le nom de la colonne doit être unique pour la table en cours de création. Il faut soit spécifier le type de données ou le nom du domaine dont les caractéristiques seront copiées pour la colonne, soit spécifier que la colonne est calculable.

Tout type de données SQL peut être utilisé comme type de colonne.

Colonnes de caractères

Pour les types CHAR, VARCHAR et BLOB avec le sous-type TEXT, vous pouvez spécifier le jeu de caractères dans la phrase CHARACTER SET. Si aucun jeu de caractères n’est spécifié, la valeur par défaut est le jeu de caractères qui a été spécifié lors de la création de la base de données. Si aucun jeu de caractères n’a été spécifié lors de la création de la base de données, le jeu de caractères par défaut est NONE. Dans ce cas, les données sont stockées et récupérées telles qu’elles ont été soumises. Il est possible de charger des données dans une colonne dans n’importe quel encodage, mais il n’est pas possible de charger ces données dans une colonne avec un encodage différent. La translittération entre les codages source et destination n’est pas effectuée, ce qui peut entraîner des erreurs.

La clause facultative COLLATE vous permet de spécifier un ordre de tri pour les types de données de type chaîne (sauf pour BLOB). Si aucun ordre de tri n’est spécifié, l’ordre de tri par défaut pour le jeu de tri spécifié est pris en charge.

Gestion de la réplication

La phrase facultative ENABLE PUBLICATION inclut la table dans le jeu de réplication (publication). Si l’instruction ALTER DATABASE ADD ALL TO PUBLICATION a été exécutée précédemment, la table sera incluse dans la publication. même si la clause ENABLE PUBLICATION n’est pas spécifiée.

La phrase facultative DISABLE PUBLICATION exclut une table d’un ensemble de réplication (publication). Cette suggestion n’a de sens que si l’instruction ALTER DATABASE ADD ALL TO PUBLICATION a été exécutée auparavant, ce qui ajoute automatiquement les tables nouvellement créées à la publication.

Qui peut créer un table ?

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

L’utilisateur qui a créé la table devient le propriétaire de la table.

Exemples

Example 1. Création d’un table
CREATE TABLE COUNTRY (
  COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
  CURRENCY VARCHAR(10) NOT NULL);
Example 2. Création d’une table avec des clés primaires et uniques nommées
CREATE TABLE STOCK (
  MODEL SMALLINT NOT NULL CONSTRAINT PK_STOCK PRIMARY KEY,
  MODELNAME CHAR(10) NOT NULL,
  ITEMID INTEGER NOT NULL,
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID));
Example 3. Créer une table et l’ajouter à un ensemble de réplication
CREATE TABLE STOCK (
  MODEL SMALLINT NOT NULL CONSTRAINT PK_STOCK PRIMARY KEY,
  MODELNAME CHAR(10) NOT NULL,
  ITEMID INTEGER NOT NULL,
  CONSTRAINT MOD_UNIQUE UNIQUE (MODELNAME, ITEMID))
ENABLE PUBLICATION;
Example 4. Table avec un champ de type tableau
CREATE TABLE JOB (
    JOB_CODE         JOBCODE NOT NULL,
    JOB_GRADE        JOBGRADE NOT NULL,
    JOB_COUNTRY      COUNTRYNAME,
    JOB_TITLE        VARCHAR(25) NOT NULL,
    MIN_SALARY       NUMERIC(18, 2) DEFAULT 0 NOT NULL,
    MAX_SALARY       NUMERIC(18, 2) NOT NULL,
    JOB_REQUIREMENT  BLOB SUB_TYPE 1,
    LANGUAGE_REQ     VARCHAR(15) [1:5],
    PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
    FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
      ON UPDATE CASCADE
      ON DELETE SET NULL,
    CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY)
);
Example 5. Création d’une table avec des contraintes de clés primaires, étrangères et uniques pour lesquelles des noms d’index personnalisés sont spécifiés.
CREATE TABLE PROJECT (
    PROJ_ID    PROJNO NOT NULL,
    PROJ_NAME  VARCHAR(20) NOT NULL UNIQUE
      USING DESC INDEX IDX_PROJNAME,
    PROJ_DESC    BLOB SUB_TYPE 1,
    TEAM_LEADER  EMPNO,
    PRODUCT      PRODTYPE,
    CONSTRAINT PK_PROJECT PRIMARY KEY (PROJ_ID)
      USING INDEX IDX_PROJ_ID,
    FOREIGN KEY (TEAM_LEADER) REFERENCES EMPLOYEE (EMP_NO)
      USING INDEX IDX_LEADER
);
Example 6. Création d’une table avec une colonne d’identité BY DEFAULT
CREATE TABLE objects (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
INSERT INTO objects (id, name) VALUES (10, 'Computer');

SELECT * FROM objects;
ID           NAME
============ ===============
           1 Table
           2 Book
          10 Computer

Création d’une table avec une colonne d’identité ALWAYS

CREATE TABLE objects (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
INSERT INTO objects (id, name) VALUES (DEFAULT, 'Computer');

SELECT * FROM objects;
ID           NAME
============ ===============
           1 Table
           2 Book
           3 Computer
Example 7. Création d’une table avec une colonne d’identité avec une valeur initiale de 10 et un incrément de 2
CREATE TABLE objects (
  id INTEGER GENERATED BY DEFAULT AS IDENTITY (STER WITH 10 INCREMENT BY 2) PRIMARY KEY,
  name VARCHAR(15)
);

INSERT INTO objects (name) VALUES ('Table');
INSERT INTO objects (name) VALUES ('Book');
ID           NAME
============ ===============
          12 Table
          14 Book
Example 8. Création d’une table avec des champs calculés
CREATE TABLE SALARY_HISTORY (
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) NOT NULL,
    OLD_SALARY      SALARY NOT NULL,
    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
    SALARY_CHANGE   GENERATED ALWAYS AS
      (OLD_SALARY * PERCENT_CHANGE / 100),
    NEW_SALARY      COMPUTED BY
      (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100)
);

Le champ SALARY_CHANGE est déclaré selon le standard SQL::2003, le champ NEW_SALARY selon le style classique de Firebird de déclaration des champs calculés.

CREATE TABLE SALARY_HISTORY
(
    EMP_NO          EMPNO NOT NULL,
    CHANGE_DATE     TIMESTAMP DEFAULT 'NOW' NOT NULL,
    UPDATER_ID      VARCHAR(20) NOT NULL,
    OLD_SALARY      SALARY NOT NULL,
    PERCENT_CHANGE  DOUBLE PRECISION DEFAULT 0 NOT NULL,
    SALARY_CHANGE   GENERATED ALWAYS AS
      (OLD_SALARY * PERCENT_CHANGE / 100),
    NEW_SALARY      COMPUTED BY
      (OLD_SALARY + OLD_SALARY * PERCENT_CHANGE / 100)
)
SQL SECURITY DEFINER;

Idem, mais les colonnes calculées le sont avec les droits de l’utilisateur qui les définit (propriétaire de la table). En outre, les triggers héritent des privilèges d’exécution des tables, s’ils ne sont pas surchargés dans les triggers eux-mêmes.

Tableaux horaires mondiaux (GTT)

Les tables temporaires globales (ci-après dénommées GTT) sont des métadonnées permanentes tout comme les tables régulières, mais les données qu’elles contiennent sont limitées dans le temps d’existence à une transaction (valeur par défaut) ou à une connexion à la base de données. Chaque transaction ou connexion possède sa propre instance de données GTT, isolée de toutes les autres. Les instances ne sont créées que si l’on accède à la GTT, et les données qu’elles contiennent sont supprimées lorsque la transaction est terminée ou déconnectée de la base de données. Les métadonnées des GTT peuvent être modifiées ou supprimées à l’aide des instructions ALTER TABLE et DROP TABLE.

Syntaxe
CREATE GLOBAL TEMPORARY TABLE name
  (<column_def> [, {<column_def> | <table_constraint>} ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]
  [SQL SECURITY {DEFINER | INVOKER}]

Si la phrase optionnelle ON COMMIT DELETE ROWS est spécifiée dans l’instruction de création de table temporaire globale, une GTT de niveau transactionnel (par défaut) sera créée. Si la phrase ON COMMIT PRESERVE ROWS est spécifiée — un niveau de connexion à la base de données GTT sera créé.

La phrase EXTERNAL [FILE] ne peut pas être utilisée pour une table temporaire globale.

Note

Les instructions COMMIT RETAINING et ROLLBACK RETAINING stockent les données dans des tables temporaires globales déclarées comme ON COMMIT DELETE ROWS. Dans Firebird 2.x il y avait un bug : COMMIT RETAINING et ROLLBACK RETAINING rendaient les enregistrements non visibles pour la transaction courante. Pour revenir au comportement de la version 2.x, définissez le ClearGTTAtRetaining à 1 dans firebird.conf. Ce paramètre peut être supprimé dans Firebird 5.0.

Restrictions GTT

Les GTT possèdent tous les attributs des tables ordinaires (clés, clés étrangères, index et déclencheurs), mais présentent un certain nombre de limitations :

  • Les tableaux GTT et réguliers ne peuvent pas se référer les uns aux autres ;

  • Les GTT de niveau connexion ("`PRESERVE ROWS'") ne peuvent pas faire référence aux GTT de niveau transaction ("`DELETE ROWS'") ;

  • Les restrictions de domaine ne peuvent pas faire référence à un GTT ;

  • La destruction d’une instance GTT à la fin de son cycle de vie ne déclenche pas de déclencheurs pré/post suppression.

Tip

Dans une base de données existante, il n’est pas toujours facile de distinguer une table ordinaire d’une TTG, ou une TTG au niveau de la transaction d’une TTG au niveau de la connexion. Utilisez la requête suivante pour identifier le type de table :

SELECT t.rdb$type_name
FROM rdb$relations r
JOIN rdb$types t ON r.rdb$relation_type = t.rdb$type
WHERE t.rdb$field_name = 'RDB$RELATION_TYPE'
  AND r.rdb$relation_name = 'TABLENAME'

Utilisez une requête pour afficher des informations sur les types de toutes les tables :

SELECT r.rdb$relation_name, t.rdb$type_name
FROM rdb$relations r
JOIN rdb$types t ON r.rdb$relation_type = t.rdb$type
WHERE t.rdb$field_name = 'RDB$RELATION_TYPE'
  AND coalesce (r.rdb$system_flag, 0) = 0

Le champ RDB$TYPE_NAME indiquera PERSISTENT pour la table régulière, VIEW pour la vue, GLOBAL_TEMPORARY_PRESERVE pour la GTT de niveau connexion et GLOBAL_TEMPORARY_DELETE pour la GTT de niveau transaction.

Exemples

Example 1. Création d’une table temporaire globale au niveau de la connexion
CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
    ID INTEGER NOT NULL PRIMARY KEY,
    TXT VARCHAR(32),
    TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
ON COMMIT PRESERVE ROWS;
Example 2. Créez une table temporaire globale de niveau transaction qui fait référence à une table temporaire globale de niveau connexion avec une clé externe.
CREATE GLOBAL TEMPORARY TABLE MYTXGTT (
    ID INTEGER NOT NULL PRIMARY KEY,
    PARENT_ID INTEGER NOT NULL REFERENCES MYCONNGTT(ID),
    TXT VARCHAR(32),
    TS TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

Tables externes

La clause optionnelle EXTERNAL [FILE] indique que la table est stockée en dehors de la base de données dans un fichier texte externe. Les colonnes d’une table stockée dans un fichier externe peuvent être de n’importe quel type, à l’exception des BLOB et des tableaux avec n’importe quel type de données.

Seules les opérations d’ajout de nouvelles lignes (INSERT) et de sélection de données (SELECT) sont autorisées sur une table stockée dans un fichier externe. Les opérations de modification des données existantes (UPDATE) ou de suppression des lignes de cette table (DELETE) ne peuvent pas être effectuées.

Une table externe ne peut pas contenir de restrictions concernant les clés primaires, externes et uniques. Aucun index ne peut être créé pour les champs d’une telle table.

Le fichier de table externe doit être situé sur un périphérique de stockage physiquement situé sur le serveur hébergeant le SGBD. Si le [paramètre]``ExternalFileAccess'' du fichier de configuration firebird.conf contient `Restrict', le fichier de la table externe doit être situé dans l’un des répertoires spécifiés dans l’argument `Restrict'. Si Firebird ne trouve pas le fichier lors de l’accès à la table, il le créera lors du premier accès.

Important

La possibilité d’utiliser des fichiers externes pour les tables dépend de la valeur du ExternalFileAccess dans le fichier de configuration firebird.conf :

  • S’il est défini à None, tout accès au fichier externe est refusé.

  • La valeur Restrict est recommandée pour limiter l’accès aux fichiers externes aux répertoires créés spécifiquement à cet effet par l’administrateur du serveur. Par exemple :

    • ExternalFileAccess = Restrict externalfiles limitera l’accès au répertoire externalfiles du répertoire racine de Firebird.

    • ExternalFileAccess = Restrict d:\databases\outfiles ; e:\infiles limitera l’accès à deux répertoires Windows seulement. Notez que les chemins qui sont des mappings de chemins de réseau ne fonctionneront pas. Les chemins entre guillemets simples ou doubles ne fonctionnent pas non plus.

  • La valeur "Full" permet d’accéder aux fichiers externes n’importe où sur le système de fichiers de l’hôte. Cela crée une vulnérabilité et n’est pas recommandé.

Format de fichier externe

La table externe est au format rows' avec une longueur fixe. Il n’y a pas de séparateurs de champs : les limites des champs et des lignes sont définies par la taille maximale en octets de chaque définition de champ. Il faut en tenir compte tant lors de la définition de la structure d’une table externe que lors de la conception d’un fichier d’entrée pour une table externe dans laquelle des données d’une autre application doivent être importées. Par exemple, le format répandu ".csv'", ne peut pas être utilisé comme fichier d’entrée, et ne peut pas être reçu directement comme fichier externe.

Le type de données le plus utile pour les colonnes de tables externes est CHAR avec une longueur fixe. La longueur doit correspondre aux données que vous souhaitez manipuler. Les types numériques et les dates peuvent facilement être convertis en eux, et les lignes sont produites telles quelles, alors que si les données ne sont pas lues par une autre base de données Firebird, les types natifs peuvent être méconnaissables pour les applications externes et être "abracadabra" pour elles.

Bien sûr, il existe des moyens de manipuler les types de données pour créer des fichiers de sortie de Firebird qui peuvent être lus directement comme fichiers d’entrée dans d’autres applications, en utilisant des procédures stockées avec ou sans utilisation de tables externes. La description de ces méthodes dépasse le cadre de ce manuel. Nous ne donnerons ici que quelques directives et conseils pour créer et travailler avec des fichiers texte simples, car une table externe est souvent utilisée comme moyen simple de créer ou de lire un journal indépendant des transactions. Ces fichiers peuvent être lus hors ligne avec un éditeur de texte ou une application d’audit.

Séparateur de ligne

En général, les fichiers externes sont plus pratiques si les lignes sont séparées par un délimiteur, comme une séquence de "nouvelle ligne" qui peut être reconnue par une application sur la plate-forme prévue. Pour Windows, il s’agit d’une séquence de deux octets "CRLF", d’un retour chariot (code ASCII 13) et d’un saut de ligne (code ASCII 10). Pour POSIX — LF est généralement autonome, dans certaines applications macOS X il peut s’agir de LFCR. Il existe plusieurs façons de remplir automatiquement la colonne du séparateur. Dans notre exemple, nous utilisons le trigger BEFORE INSERT et la fonction intégrée ASCII_CHAR.

Exemples d’utilisation de tables externes

Dans notre exemple, nous allons définir une table d’enregistrement externe qui peut être utilisée dans le gestionnaire d’exception d’une procédure stockée ou d’un déclencheur. La table externe est choisie parce que les messages de toute exception traitée seront stockés dans le journal, même si la transaction dans laquelle le processus a été exécuté est annulée en raison d’une autre exception non traitée. À des fins de démonstration, notre tableau ne contient que deux colonnes : un horodatage et un message texte. La troisième colonne stocke le séparateur de ligne :

CREATE TABLE ext_log
EXTERNAL FILE 'd:\externals\log_me.txt' (
   stamp   CHAR(24),
   message CHAR(100),
   crlf    CHAR(2) -- Pour Windows
);

COMMIT;

Créez maintenant un déclencheur pour enregistrer automatiquement l’horodatage et le séparateur de ligne chaque fois qu’un message est écrit dans la table :

SET TERM ^;
CREATE TRIGGER bi_ext_log FOR ext_log
ACTIVE BEFORE INSERT
AS
BEGIN
  IF (NEW.stamp IS NULL) THEN
    NEW.stamp = CAST (CURRENT_TIMESTAMP AS CHAR(24));
  NEW.crlf = ASCII_CHAR(13) || ASCII_CHAR(10);
END ^
COMMIT ^
SET TERM ;^

Insertion de certaines entrées (cela peut être fait dans un gestionnaire d’exception)

INSERT INTO ext_log (message)
VALUES('Shall I compare thee to a summer''s day?');
INSERT INTO ext_log (message)
VALUES('Thou art more lovely and more temperate');

Contenu du fichier externe:

2015-10-07 15:19:03.4110Shall I compare thee to a summer's day?
2015-10-07 15:19:58.7600Thou art more lovely and more temperate
Voir aussi :

ALTER TABLE, DROP TABLE, CREATE DOMAIN.

Restriction NOT NULL

Par défaut, la colonne peut accepter la valeur NULL.

La clause facultative NOT NULL indique que la colonne ne peut pas être affectée d’une valeur NULL.

Valeur par défaut

La clause facultative DEFAULT vous permet de spécifier une valeur par défaut pour une colonne de table. Cette valeur sera placée dans la colonne de la table lors de l’exécution de l’instruction INSERT si aucune valeur n’est spécifiée pour cette colonne. La valeur par défaut peut être un littéral compatible avec le type, une valeur NULL inconnue, ou une variable contextuelle dont le type est compatible avec le type de la colonne. Si aucune valeur par défaut n’est explicitement définie, une valeur vide, NULL, est supposée. L’utilisation d’expressions dans la valeur par défaut n’est pas autorisée.

Colonnes basées sur un domaine

Pour définir une colonne, on peut utiliser le domaine décrit précédemment. Si la définition de la colonne est basée sur un domaine, elle peut inclure une nouvelle valeur par défaut, des contraintes CHECK supplémentaires, une suggestion COLLATE qui remplacent les valeurs spécifiées dans la définition du domaine. Une telle définition de colonne peut inclure des contraintes de colonne supplémentaires telles que NOT NULL si le domaine n’en contient pas déjà une.

Important

Notez que si NOT NULL a été spécifié dans la définition du domaine, il n’est pas possible de définir l’admissibilité de NULL au niveau de la colonne. Si vous voulez pouvoir définir des colonnes autorisant la pseudo-définition NULL et ne l’autorisant pas, c’est une bonne pratique de créer un domaine autorisant NULL et de spécifier une contrainte NOT NULL sur les colonnes de la table où cela est nécessaire.

Colonnes d’identité (auto-incrémentation)

La colonne d’identité est une colonne associée au générateur de séquence interne. Les colonnes d’identité peuvent être définies soit par la phrase GENERATED BY DEFAULT AS IDENTITY, soit par la phrase GENERATED ALWAYS AS IDENTITY.

Si la colonne d’identité est spécifiée comme Générée par défaut, alors sa valeur sera incrémentée et utilisée comme valeur par défaut à chaque insertion, seulement si la valeur de cette colonne n’est pas explicitement spécifiée.

Pour utiliser la valeur par défaut générée, vous devez soit spécifier le mot clé DEFAULT lors de l’insertion dans la colonne d’identité, soit simplement ne pas mentionner la colonne d’identité dans la liste des colonnes à insérer. Sinon, la valeur que vous avez spécifiée sera utilisée.

Example 1. Colonne définie comme GENERATED BY DEFAULT AS IDENTITY
CREATE TABLE greetings (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  name CHAR(50));

-- specify value "1":
INSERT INTO greetings VALUES (1, 'hi');

-- use generated default
INSERT INTO greetings VALUES (DEFAULT, 'salut');

-- use generated default
INSERT INTO greetings(ch) VALUES ('bonjour');
Note

Ce comportement peut être modifié dans l’instruction INSERT si la directive `OVERRIDING USER VALUE' est spécifiée. Voir OVERRIDING directive pour plus de détails.

Si la colonne d’identité est spécifiée comme GENERATED ALWAYS', sa valeur s’incrémentera chaque fois qu’elle sera insérée. Si vous tentez d’attribuer explicitement une valeur de colonne d’identité dans l’instruction `INSERT, vous obtiendrez un message d’erreur. Dans l’instruction INSERT, vous pouvez spécifier le mot-clé DEFAULT au lieu d’une valeur pour la colonne d’identité.

create table greetings (
  id INT GENERATED ALWAYS AS IDENTITY,
  name CHAR(50));

INSERT INTO greetings VALUES (DEFAULT, 'hello');

INSERT INTO greetings(ch) VALUES ('bonjour');
Note

Ce comportement peut être modifié dans l’instruction INSERT si la directive OVERRIDING SYSTEM VALUE est spécifiée. Voir directive OVERRIDING pour plus de détails.

La phrase optionnelle START WITH vous permet de spécifier une valeur de départ autre que zéro. La phrase INCREMENT [BY]CREATE TABLE, IDENTITY, INCREMENT BY définit la valeur d’incrémentation. La valeur de l’incrément doit être différente de 0. Par défaut, la valeur de l’incrément est de 1.

règles

  • Le type de données de la colonne d’identité doit être un nombre entier avec une échelle de zéro. Les types acceptables sont SMALLINT, INTEGER, BIGINT, NUMERIC(x,0) et DECIMAL(x,0) ;

  • La colonne d’identification ne peut pas avoir les valeurs DEFAULT et COMPUTED.

Note

  • Une colonne d’identité peut être modifiée pour devenir une colonne normale. Une colonne normale ne peut pas être modifiée pour devenir une colonne d’identité.

  • Les colonnes d’identification sont implicitement des colonnes `NOT NULL'.

  • L’unicité n’est pas automatiquement garantie. Les restrictions UNIQUE ou PRIMARY KEY sont nécessaires pour garantir l’unicité.
Voir aussi :

Directive OVERRIDING.

Champs calculés

Les champs calculables peuvent être définis avec une phrase COMPUTED [BY] ou GENERATED ALWAYS AS (selon la norme SQL-2003). Ils sont équivalents en termes de signification. Pour les champs évaluables, il n’est pas nécessaire de décrire le type de données (mais cela est permis), le SGBD calcule un type approprié à la suite de l’analyse de l’expression. L’expression nécessite de spécifier une opération correcte pour les types de données de la colonne qu’elle contient. Si vous spécifiez explicitement un type de colonne pour le champ à évaluer, le résultat du calcul est converti au type spécifié, c’est-à-dire que le résultat d’une expression numérique peut être édité sous forme de chaîne de caractères, par exemple. L’expression est évaluée pour chaque ligne des données sélectionnées si l’instruction de sélection de données SELECT contient une telle colonne.

Tip

Au lieu d’utiliser une colonne calculée, dans certains cas, il est judicieux d’utiliser une colonne ordinaire dont la valeur est calculée dans les déclencheurs d’ajout et de mise à jour de données. Cela peut réduire la performance de l’insertion/modification des enregistrements, mais améliorera la performance de l’extraction des données.

Colonnes de type tableau

Pour tout type de données autre que BLOB, vous pouvez spécifier une dimension de tableau si la colonne doit être un tableau. La dimension du tableau est spécifiée entre crochets. Ils sont indiqués en gras pour éviter toute confusion avec les symboles indiquant des éléments facultatifs. Deux nombres séparés par un deux-points sont spécifiés lors de la spécification de la dimension d’un tableau. Le premier chiffre est le chiffre de départ, le second est le chiffre d’arrivée. Si un seul nombre est spécifié, il s’agit du dernier nombre dans l’élément du tableau, et le premier nombre est 1. Pour un tableau multidimensionnel, les dimensions du tableau sont listées avec une virgule.

Restrictions

Il existe quatre types de restrictions :

  • clé primaire (PRIMARY KEY) ;

  • Une clé unique (UNIQUE) ;

  • clé étrangère (REFERENCES ou FOREIGN KEY) ;

  • vérifier la contrainte (CHECK).

Les contraintes peuvent être spécifiées au niveau de la colonne ("`Column Constraints'") ou au niveau de la table ("`Table Constraints'"). Les contraintes au niveau de la table sont nécessaires lorsque les clés (contrainte d’unicité, clé primaire ou clé étrangère) doivent être générées à travers plusieurs colonnes ou lorsqu’une contrainte CHECK implique plusieurs colonnes, c’est-à-dire fonctionne à un niveau record. La syntaxe de certains types de contraintes peut varier légèrement selon que la contrainte est définie au niveau de la colonne ou de la table.

  • Une contrainte de niveau colonne est spécifiée après que les autres caractéristiques de la colonne aient été définies. Elle ne peut inclure que la colonne spécifiée dans cette définition.

  • Les contraintes au niveau de la table sont spécifiées après toutes les définitions de colonnes. Les contraintes au niveau de la table sont un moyen plus polyvalent d’écrire des contraintes car elles permettent d’appliquer une contrainte à plus d’une colonne de la table.

  • Vous pouvez mélanger les contraintes de colonne et de table dans une seule instruction CREATE TABLE.

Un index est automatiquement créé par le système pour la clé primaire (PRIMARY KEY), la clé unique (UNIQUE KEY) et la clé étrangère (REFERENCES pour les restrictions au niveau des colonnes, et FOREIGN KEY REFERENCES pour les restrictions au niveau des tables).

Noms des contraintes et de leurs indices

Si un nom de contrainte n’est pas spécifié, il sera automatiquement généré par le système.

Les contraintes de niveau colonne et leurs indices sont automatiquement nommés comme suit :

  • Les noms des contraintes sont de la forme INTEG_<n> où n est représenté par un ou plusieurs nombres ;

  • Les noms d’index ont la forme RDB$PRIMARY<n> (pour un index de clé primaire), RDB$FOREIGN<n> (pour un index de clé étrangère) ou RDB$<n> (pour un index de clé unique), où n est représenté par un ou plusieurs chiffres ;

Les schémas de dénomination automatique pour les contraintes de niveau table et leurs index sont les mêmes.

Contraintes nommées

Le nom de la contrainte peut être défini explicitement en le spécifiant dans la clause optionnelle CONSTRAINT. Par défaut, le nom de l’index de la contrainte sera le même que celui de la contrainte elle-même. Si un nom différent est requis pour l’index, il peut être spécifié dans la clause USING.

Contraintes USING

La condition USING permet un nom défini par l’utilisateur pour l’index généré automatiquement pour la contrainte, et définit optionnellement si l’index sera en ordre ascendant (par défaut) ou descendant.

Clé primaire (PRIMARY KEY)

La contrainte de clé primaire PRIMARY KEY est construite sur un champ avec une contrainte NOT NULL donnée et exige que les valeurs des colonnes soient uniques. Une table ne peut avoir qu’une seule clé primaire.

  • Une clé primaire à une seule colonne peut être définie au niveau de la colonne ou de la table.

  • Une clé primaire pour plusieurs colonnes ne peut être définie qu’au niveau de la table.

contrainte clés unique (UNIQUE)

La contrainte UNIQUE de clé unique spécifie une exigence d’unicité pour les valeurs de la colonne. Une table peut contenir un nombre quelconque de clés uniques.

Comme une clé primaire, la contrainte d’unicité peut être définie sur plus d’une colonne. Dans ce cas, vous devez la définir comme une contrainte de niveau table.

NULL dans les clés uniques

Selon la norme SQL-99, Firebird autorise une ou plusieurs valeurs NULL dans une colonne à laquelle est appliquée une contrainte UNIQUE. Cela vous permet de définir une contrainte UNIQUE sur des colonnes qui n’ont pas de contrainte NOT NULL.

Pour les clés uniques contenant plusieurs colonnes, la logique est un peu plus compliquée :

  • Les entrées multiples avec une valeur NULL dans toutes les colonnes de la clé sont autorisées ;

  • Plusieurs enregistrements avec différentes combinaisons de valeurs nulles et non nulles dans les clés sont autorisés ;

  • Allowed est un ensemble d’enregistrements dans lequel l’une des colonnes de la clé unique contient des valeurs NULL mais les autres colonnes sont remplies de valeurs et ces valeurs sont différentes dans au moins l’une d’entre elles ;

  • Les enregistrements multiples sont autorisés, dans lesquels l’une des colonnes de la clé unique contient une valeur NULL et les autres colonnes sont remplies de valeurs, et ces valeurs ont une correspondance dans au moins l’une d’entre elles.

Cela peut être résumé par l’exemple suivant :

RECREATE TABLE t( x int, y int, z int, unique(x,y,z));
INSERT INTO t values( NULL, 1, 1 );
INSERT INTO t values( NULL, NULL, 1 );
INSERT INTO t values( NULL, NULL, NULL );
INSERT INTO t values( NULL, NULL, NULL ); -- Autorisé
INSERT INTO t values( NULL, NULL, 1 ); -- Interdit

contrainte de clé étrangère (FOREIGN KEY)

La contrainte de clé étrangère garantit que la ou les colonnes membres ne peuvent contenir que des valeurs qui existent dans la ou les colonnes spécifiées de la table principale. Ces colonnes de référence sont souvent appelées colonnes de destination. Ils doivent être la clé primaire ou une clé unique dans la table de destination. Ils peuvent ne pas avoir de contrainte NOT NULL` s’ils font partie de la contrainte de clé unique.

Les colonnes de clés externes ne nécessitent pas de contrainte `NOT NULL'.

Au niveau de la colonne, la contrainte de clé étrangère est définie en utilisant le mot-clé REFERENCES.

... ,
ARTIFACT_ID INTEGER REFERENCES COLLECTION (ARTIFACT_ID),

Dans cet exemple, la colonne ARTIFACT_ID fait référence à une colonne du même nom dans la table COLLECTION.

Une clé étrangère sur une ou plusieurs colonnes peut être définie au niveau de la table. Les clés étrangères sur plusieurs colonnes ne peuvent être définies qu’au niveau de la table.

La syntaxe pour définir une clé étrangère au niveau de la table est légèrement différente. Après avoir défini toutes les colonnes, avec leurs contraintes de niveau colonne, vous pouvez définir une contrainte de clé étrangère de niveau table nommée en utilisant les mots-clés FOREIGN KEY et les noms des colonnes pour lesquelles elle s’applique :

... ,
CONSTRAINT FK_ARTSOURCE FOREIGN KEY(DEALER_ID, COUNTRY)
  REFERENCES DEALER (DEALER_ID, COUNTRY),

Notez que les noms des colonnes dans la table cible (maître) peuvent être différents de ceux de la clé externe.

Note

Si aucune colonne cible n’est spécifiée, la clé étrangère fait automatiquement référence aux colonnes de la clé primaire de la table cible.
Actions clés externes

Pour une intégrité des données supplémentaire, des options facultatives peuvent être spécifiées pour assurer la cohérence des données entre les tables parent et enfant selon les règles spécifiées :

  • La phrase ON UPDATE spécifie ce qui arrive aux entrées d’une sous table lorsque la valeur de la clé primaire/unique d’une ligne de la table parent est modifiée.

  • La clause ON DELETE spécifie ce qui arrive aux entrées de la sous table lorsque la ligne correspondante dans la table principale est supprimée.

Pour garantir l’intégrité référentielle d’une clé étrangère lorsque la valeur d’une clé primaire ou unique connexe est modifiée ou supprimée, les actions suivantes peuvent être effectuées :

  • NO ACTION (par défaut) — aucune action ne sera effectuée ;

  • CASCADE — Lorsqu’une valeur de clé primaire est modifiée ou supprimée, les mêmes actions seront effectuées sur la valeur de la clé étrangère. Lors de la suppression d’une ligne dans la table principale, tous les enregistrements ayant les mêmes valeurs de clé étrangère que la valeur de clé primaire (unique) de la ligne de la table principale supprimée doivent être supprimés dans la sous-table. Lors de la mise à jour d’un enregistrement de la table principale dans une table secondaire, toutes les valeurs de la clé étrangère ayant les mêmes valeurs que la valeur de la clé primaire (unique) de la ligne de la table principale qui est modifiée doivent être modifiées ;

  • SET DEFAULT — les valeurs des clés étrangères de toutes les lignes correspondantes dans la sous-table sont définies à la valeur par défaut spécifiée dans la clause DEFAULT pour cette colonne ;

  • SET NULL — les valeurs des clés étrangères de toutes les lignes correspondantes dans la sous table sont mises à NULL.

Example 1. Clé externe avec mise à jour en cascade et mise à NULL en cas de suppression
CONSTRAINT FK_ORDERS_CUST
FOREIGN KEY (CUSTOMER) REFERENCES CUSTOMERS (ID)
ON UPDATE CASCADE ON DELETE SET NULL

Contrainte CHECK

La contrainte CHECK spécifie la condition que les valeurs placées dans cette colonne doivent satisfaire. Une condition est une expression logique, également appelée prédicat, qui peut renvoyer les valeurs TRUE, FALSE, et UNKNOWN. La condition est satisfaite si le prédicat renvoie TRUE ou UNKNOWN (équivalent à NULL). Si le prédicat renvoie FALSE, la valeur ne sera pas acceptée. Cette condition est utilisée lors de l’ajout d’une nouvelle ligne à la table (l’opérateur INSERT) et lors du changement d’une valeur existante d’une colonne de la table (l’opérateur UPDATE) ainsi que les opérateurs dans lesquels une de ces actions peut se produire (UPDATE OR INSERT, MERGE).

Important

Lorsque vous utilisez la phrase CHECK pour une colonne basée sur un domaine, n’oubliez pas que l’expression dans CHECK ne fait que compléter une condition de vérification qui peut déjà être définie dans le domaine.

Au niveau de la colonne ou de la table, l’expression dans la phrase CHECK fait référence à la valeur entrante en utilisant les identifiants de colonne, contrairement aux domaines où la contrainte CHECK utilise le mot-clé VALUE à cette fin.

Example 1. Vérifier les limites des niveaux de colonnes et de tables
CREATE TABLE PLACES (
  ...
  LAT DECIMAL(9, 6) CHECK (ABS(LAT) <= 90),
  LON DECIMAL(9, 6) CHECK (ABS(LON) <= 180),
  ...
  CONSTRAINT CHK_POLES CHECK (ABS(LAT) < 90 OR LON = 0)
);

Privilèges d’exécution

La clause optionnelle SQL SECURITY dans la spécification de la table permet de spécifier avec quels privilèges les colonnes calculées sont calculées. Si l’option INVOKER est sélectionnée, les colonnes calculées le sont 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. En outre, les triggers héritent des privilèges d’exécution des tables, s’ils ne sont pas surchargés dans les triggers eux-mêmes.

Tip

Les privilèges d’exécution par défaut pour les objets de métadonnées nouvellement créés peuvent être modifiés à l’aide de la commande

ALTER DATABASE SET DEFAULT SQL SECURITY {DEFINER | INVOKER}

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).

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.