CREATE TABLE

CREATE TABLE

Global Temporary Tables (GTTs)
GENERATED ALWAYS AS
CHECK accepts NULL outcome
Context variables as column defaults
FOREIGN KEY without target column
references PK
FOREIGN KEY creation no longer
requires exclusive access
UNIQUE constraints now allow NULLs
USING INDEX subclause

`CREATE TABLE`

Available in: DSQL, ESQL

Global Temporary Tables (GTTs)

Added in: 2.1

Description

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, and destroyed upon transaction end or disconnection. To modify or remove a GTT's metadata, ALTER TABLE and DROP TABLE can be used.

Syntax

 CREATE GLOBAL TEMPORARY TABLE name 
   (column_def [, column_def | table_constraint ...] )
   [ON COMMIT {DELETE | PRESERVE} ROWS]

ON COMMIT DELETE ROWS creates a transaction-level GTT (the default), ON COMMIT PRESERVE ROWS a connection-level GTT.
An EXTERNAL [FILE] clause is not allowed on a global temporary table.

Restrictions: GTTs can be "dressed up" with all the features and paraphernalia of ordinary tables (keys, references, indices, triggers...) 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 life cycle does not cause any before/after delete triggers to fire.

Example

 create global temporary table MyConnGTT (
   id int not null primary key,
   txt varchar(32),
   ts timestamp default current_timestamp
 )
 on commit preserve rows;

 commit;

 create global temporary table MyTxGTT (
   id int not null primary key,
   parent_id int not null references MyConnGTT(id),
   txt varchar(32),
   ts timestamp default current_timestamp
 );

 commit;

Tip: In an existing database, it's not always easy to tell a regular table from a GTT, or a transaction-level GTT from a connection-level GTT. Use this query to find out a table's type:

 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'

Or, for an overview of all your relations:

 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

`GENERATED ALWAYS AS`

Added in: 2.1

Description

Instead of COMPUTED [BY], you may also use the SQL-2003-compliant equivalent GENERATED ALWAYS AS for computed fields.

Syntax

 colname [coltype] GENERATED ALWAYS AS (expression)

Example

 create table Persons (
   id int primary key,
   firstname varchar(24) not null,
   middlename varchar(24),
   lastname varchar(24) not null,
   fullname varchar(74) generated always as
     (firstname || coalesce(' ' || middlename, '') || ' ' || lastname),
   street varchar(32),
   ...
   ...
 )

Note: GENERATED ALWAYS AS is not currently supported in index definitions.

`CHECK` accepts `NULL` outcome

Changed in: 2.0

Description

If a CHECK constraint resolves to NULL, Firebird versions before 2.0 reject the input. Following the SQL standard to the letter, Firebird 2.0 and above let NULLs pass and only consider the check failed if the outcome is false.

Example

Checks like these:

 check (value > 10000) 

 check (Town like 'Amst%')

 check (upper(value) in ( 'A', 'B', 'X' ))

 check (Minimum <= Maximum)

all fail in pre-2.0 Firebird versions if the value to be checked is NULL. In 2.0 and above they succeed.

Warning: This change may cause existing databases to behave differently when migrated to Firebird 2.0+. Carefully examine your CREATE/ALTER TABLE statements and add "and XXX is not null" predicates to your CHECKs if they should continue to reject NULL input.

Context variables as column defaults

Changed in: IB

Description

Any context variable that is assignment-compatible to the column datatype can be used as a default. This was already the case in InterBase 6, but the Language Reference only mentioned USER.

Example

 create table MyData (
   id int not null primary key,
   record_created timestamp default current_timestamp,
   ...
 )

`FOREIGN KEY` without target column references PK

Changed in: IB

Description

If you create a foreign key without specifying a target column, it will reference the primary key of the target table. This was already the case in InterBase 6, but the IB Language Reference wrongly states that in such cases, the engine scans the target table for a column with the same name as the referencing column.

Example

 create table eik (
   a int not null primary key,
   b int not null unique
 );

 create table beuk ( 
   b int references eik
 );

 -- beuk.b references eik.a, not eik.b !

`FOREIGN KEY` creation no longer requires exclusive access

Changed in: 2.0

Description

In Firebird 2.0 and above, creating a foreign key constraint no longer requires exclusive access to the database.

`UNIQUE` constraints now allow `NULL`s

Changed in: 1.5

Description

In compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns with a UNIQUE constraint. It is therefore possible to define a UNIQUE key on a column that has no NOT NULL constraint.

For UNIQUE keys that span multiple columns, the logic is a little complicated:

Multiple rows having all the UK columns NULL are allowed.
Multiple rows having a different subset of UK colums NULL are allowed.
Multiple rows having the same subset of UK columns NULL and the rest filled with regular values and those regular values differ in at least one column, are allowed.
Multiple rows having the same subset of UK columns NULL and the rest filled with regular values and those regular values are the same in every column, are forbidden.

One way of summarizing this is as follows: In principle, all NULLs are considered distinct. But if two rows have exactly the same subset of UK columns filled with non-NULL values, the NULL columns are ignored and the non-NULL columns are decisive, just as if they constituted the entire unique key.

`USING INDEX` subclause

Available in: DSQL

Added in: 1.5

Description

A USING INDEX subclause can be placed at the end of a primary, unique or foreign key definition.

Its purpose is to

provide a user-defined name for the automatically created index that enforces the constraint, and
optionally define the index to be ascending or descending (the default being ascending).

Without USING INDEX, indices enforcing named constraints are named after the constraint (this is new behaviour in Firebird 1.5) and indices for unnamed constraints get names like RDB$FOREIGN13 or something equally romantic.

Note: You must always provide a new name for the index. It is not possible to use pre-existing indices to enforce constraints.

USING INDEX can be applied at field level, at table level, and (in ALTER TABLE) with ADD CONSTRAINT. It works with named as well as unnamed key constraints. It does not work with CHECK constraints, as these don't have their own enforcing index.

Syntax

 [CONSTRAINT constraint-name]
   <constraint-type> <constraint-definition>
   [USING [ASC[ENDING] | DESC[ENDING]] INDEX index_name]

Examples

The first example creates a primary key constraint PK_CUST using an index named IX_CUSTNO:

 create table customers (
   custno int not null constraint pk_cust primary key using index ix_custno,
   ...

This, however:

 create table customers (
   custno int not null primary key using index ix_custno,
   ...

...will give you a PK constraint called INTEG_7 or something similar, and an index IX_CUSTNO.

Some more examples:

 create table people (
   id int not null,
   nickname varchar(12) not null,
   country char(4),
   ..
   ..
   constraint pk_people primary key (id),
   constraint uk_nickname unique (nickname) using index ix_nick
 )

 alter table people
   add constraint fk_people_country
   foreign key (country) references countries(code)
   using desc index ix_people_country

Important: If you define a descending constraint-enforcing index on a primary or unique key, be sure to make any foreign keys referencing it descending as well.