Firebird Documentation Index → Firebird 2.1 Language Ref. Update → DDL statements → TABLE
Firebird Home Firebird Home Prev: SEQUENCE or GENERATORFirebird Documentation IndexUp: DDL statementsNext: TRIGGER

TABLE

Table of Contents

CREATE TABLE
ALTER TABLE
RECREATE TABLE

Tip

Find a more recent version at Firebird 5.0 Language Reference: TABLE

CREATE TABLE

Table of Contents

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

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 NULLs

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.

ALTER TABLE

Table of Contents

ADD column: Context variables as defaults
ALTER COLUMN: DROP DEFAULT
ALTER COLUMN: SET DEFAULT
ALTER COLUMN: POSITION now 1-based
CHECK accepts NULL outcome
FOREIGN KEY without target column references PK
FOREIGN KEY creation no longer requires exclusive access
GENERATED ALWAYS AS
UNIQUE constraints now allow NULLs
USING INDEX subclause

Available in: DSQL, ESQL

ADD column: Context variables as defaults

Changed in: IB

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

Example: 

alter table MyData
  add MyDay date default current_date

ALTER COLUMN: DROP DEFAULT

Available in: DSQL

Added in: 2.0

Description: Firebird 2 adds the possibility to drop a column-level default. Once the default is dropped, there will either be no default in place or – if the column's type is a DOMAIN with a default – the domain default will resurface.

Syntax: 

ALTER TABLE tablename ALTER [COLUMN] colname DROP DEFAULT

Example: 

alter table Trees alter Girth drop default

An error is raised if you use DROP DEFAULT on a column that doesn't have a default or whose effective default is domain-based.

ALTER COLUMN: SET DEFAULT

Available in: DSQL

Added in: 2.0

Description: Firebird 2 adds the possibility to set/alter defaults on existing columns. If the column already had a default, the new default will replace it. Column-level defaults always override domain-level defaults.

Syntax: 

ALTER TABLE tablename ALTER [COLUMN] colname SET DEFAULT <default>

<default>  ::=  literal-value | context-variable | NULL

Example: 

alter table Customers alter EnteredBy set default current_user

Tip

If you want to switch off a domain-based default on a column, set the column default to NULL.

ALTER COLUMN: POSITION now 1-based

Changed in: 1.0

Description: When changing a column's position, the engine now interprets the new position as 1-based. This is in accordance with the SQL standard and the InterBase documentation, but in practice InterBase interpreted the position as 0-based.

Syntax: 

ALTER TABLE tablename ALTER [COLUMN] colname POSITION <newpos>

<newpos>  ::=  an integer between 1 and the number of columns

Example: 

alter table Stock alter Quantity position 3

Note

Don't confuse this with the POSITION in CREATE/ALTER TRIGGER. Trigger positions are and will remain 0-based.

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. For more information see under CREATE TABLE.

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

alter table beuk
  add constraint fk_beuk
  foreign key (b) references eik;

-- beuk.b now 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, adding a foreign key constraint no longer requires exclusive access to the database.

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: 

alter table Friends
  add fullname varchar(74)
  generated always as
    (firstname || coalesce(' ' || middlename, '') || ' ' || lastname)

UNIQUE constraints now allow NULLs

Changed in: 1.5

Description: In compliance with the SQL-99 standard, NULLs – even multiple – are now allowed in columns with a UNIQUE constraint. For a full discussion, see CREATE TABLE :: UNIQUE constraints now allow NULLs.

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

Syntax: 

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

For a full discussion and examples, see CREATE TABLE :: USING INDEX subclause.

RECREATE TABLE

Available in: DSQL

Added in: 1.0

Description: Creates or recreates a table. If a table with the same name already exists, RECREATE TABLE will try to drop it (destroying all its data in the process!) and create a new table. RECREATE TABLE will fail if the existing table is in use.

Syntax: Exactly the same as CREATE TABLE.

Prev: SEQUENCE or GENERATORFirebird Documentation IndexUp: DDL statementsNext: TRIGGER
Firebird Documentation Index → Firebird 2.1 Language Ref. Update → DDL statements → TABLE