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.