FirebirdSQL logo
 DOMAININDEX 

CREATE TABLE Examples

  1. Creating the COUNTRY table with the primary key specified as a column constraint.

    CREATE TABLE COUNTRY (
      COUNTRY COUNTRYNAME NOT NULL PRIMARY KEY,
      CURRENCY VARCHAR(10) NOT NULL
    );
  2. Creating the STOCK table with the named primary key specified at the column level and the named unique key specified at the table level.

    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)
    );
  3. Creating the JOB table with a primary key constraint spanning two columns, a foreign key constraint for the COUNTRY table and a table-level CHECK constraint.The table also contains an array of 5 elements.

    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),
      FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
      ON UPDATE CASCADE
      ON DELETE SET NULL,
      CONSTRAINT CHK_SALARY CHECK (MIN_SALARY < MAX_SALARY)
    );
  4. Creating the PROJECT table with primary, foreign and unique key constraints with custom index names specified with the USING clause.

    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
    );
  5. Creating a table with an identity column

    create table objects (
      id integer generated by default as identity primary key,
      name varchar(15)
    );
    
    insert into objects (name) values ('Table');
    insert into objects (id, name) values (10, 'Computer');
    insert into objects (name) values ('Book');
    
    select * from objects order by id;
    
              ID NAME
    ============ ===============
               1 Table
               2 Book
              10 Computer
  6. Creating the SALARY_HISTORY table with two computed fields.The first one is declared according to the SQL standard, while the second one is declared according to the traditional declaration of computed fields in Firebird.

    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)
    );
  7. With DEFINER set for table t, user US needs only the SELECT privilege on t.If it were set for INVOKER, the user would also need the EXECUTE privilege on function f.

    set term ^;
    create function f() returns int
    as
    begin
        return 3;
    end^
    set term ;^
    create table t (i integer, c computed by (i + f())) SQL SECURITY DEFINER;
    insert into t values (2);
    grant select on table t to user us;
    
    commit;
    
    connect 'localhost:/tmp/7.fdb' user us password 'pas';
    select * from t;
  8. With DEFINER set for table tr, user US needs only the INSERT privilege on tr.If it were set for INVOKER, either the user or the trigger would also need the INSERT privilege on table t.The result would be the same if SQL SECURITY DEFINER were specified for trigger tr_ins:

    create table tr (i integer) SQL SECURITY DEFINER;
    create table t (i integer);
    set term ^;
    create trigger tr_ins for tr after insert
    as
    begin
      insert into t values (NEW.i);
    end^
    set term ;^
    grant insert on table tr to user us;
    
    commit;
    
    connect 'localhost:/tmp/29.fdb' user us password 'pas';
    insert into tr values(2);

Global Temporary Tables (GTT)

Global temporary tables have persistent metadata, but their contents are transaction-bound (the default) or connection-bound.Every transaction or connection has its own private instance of a GTT, isolated from all the others.Instances are only created if and when the GTT is referenced.They are destroyed when the transaction ends or on disconnect.The metadata of a GTT can be modified or removed using ALTER TABLE and DROP TABLE, respectively.

Syntax
CREATE GLOBAL TEMPORARY TABLE tablename
  (<column_def> [, {<column_def> | <table_constraint>} ...])
  [<gtt_table_attrs>]

<gtt_table_attrs> ::= <gtt_table_attr> [gtt_table_attr> ...]

<gtt_table_attr> ::=
    <sql_security>
  | ON COMMIT {DELETE | PRESERVE} ROWS
Note
Syntax notes
  • ON COMMIT DELETE ROWS creates a transaction-level GTT (the default), ON COMMIT PRESERVE ROWS a connection-level GTT

  • The EXTERNAL [FILE] clause is not allowed in the definition of a global temporary table

GTTs are writable in read-only transactions.The effect is as follows:

Read-only transaction in read-write database

Writable in both ON COMMIT PRESERVE ROWS and ON COMMIT DELETE ROWS

Read-only transaction in read-only database

Writable in ON COMMIT DELETE ROWS only

Restrictions on GTTs

GTTs can be “dressed up” with all the features of ordinary tables (keys, references, indexes, triggers and so on), but there are a few restrictions:

  • GTTs and regular tables cannot reference one another

  • A connection-bound (“PRESERVE ROWS”) GTT cannot reference a transaction-bound (“DELETE ROWS”) GTT

  • Domain constraints cannot reference any GTT

  • The destruction of a GTT instance at the end of its lifecycle does not cause any BEFORE/AFTER delete triggers to fire

Tip

In an existing database, it is not always easy to distinguish a regular table from a GTT, or a transaction-level GTT from a connection-level GTT.Use this query to find out what type of table you are looking at:

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'

For an overview of the types of all the relations in the database:

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

The RDB$TYPE_NAME field will show PERSISTENT for a regular table, VIEW for a view, GLOBAL_TEMPORARY_PRESERVE for a connection-bound GTT and GLOBAL_TEMPORARY_DELETE for a transaction_bound GTT.

Examples of Global Temporary Tables

  1. Creating a connection-scoped global temporary table.

    CREATE GLOBAL TEMPORARY TABLE MYCONNGTT (
      ID  INTEGER NOT NULL PRIMARY KEY,
      TXT VARCHAR(32),
      TS  TIMESTAMP DEFAULT CURRENT_TIMESTAMP)
    ON COMMIT PRESERVE ROWS;
  2. Creating a transaction-scoped global temporary table that uses a foreign key to reference a connection-scoped global temporary table.The ON COMMIT sub-clause is optional because DELETE ROWS is the default.

    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
    ) ON COMMIT DELETE ROWS;