CREATE TABLE

`CREATE TABLE` Examples

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

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

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

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

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

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

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;

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

CREATE TABLE

CREATE TABLE Examples

Global Temporary Tables (GTT)

`CREATE TABLE` Examples