The Firebird 5.0 Language Reference

As a relational DBMS, Firebird stores data in tables.A table is a flat, two-dimensional structure containing any number of rows.Table rows are often called records.

All rows in a table have the same structure and consist of columns.Table columns are often called fields.A table must have at least one column.Each column contains a single type of SQL data.

This section describes how to create, alter and drop tables in a database.

`CREATE TABLE`

Creates a table

Available in

DSQL, ESQL

Syntax

CREATE [GLOBAL TEMPORARY] TABLE tablename
  [EXTERNAL [FILE] 'filespec']
  (<col_def> [, {<col_def> | <tconstraint>} ...])
  [{<table_attrs> | <gtt_table_attrs>}]

<col_def> ::=
    <regular_col_def>
  | <computed_col_def>
  | <identity_col_def>

<regular_col_def> ::=
  colname {<datatype> | domainname}
  [DEFAULT {<literal> | NULL | <context_var>}]
  [<col_constraint> ...]
  [COLLATE collation_name]

<computed_col_def> ::=
  colname [{<datatype> | domainname}]
  {COMPUTED [BY] | GENERATED ALWAYS AS} (<expression>)

<identity_col_def> ::=
  colname {<datatype> | domainname}
  GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY
  [(<identity_col_option>...)]
  [<col_constraint> ...]

<identity_col_option> ::=
    START WITH start_value
  | INCREMENT [BY] inc_value

<datatype> ::=
    <scalar_datatype> | <blob_datatype> | <array_datatype>

<scalar_datatype> ::=
  !! See Scalar Data Types Syntax !!

<blob_datatype> ::=
  !! See BLOB Data Types Syntax !!

<array_datatype> ::=
  !! See Array Data Types Syntax !!

<col_constraint> ::=
  [CONSTRAINT constr_name]
    { PRIMARY KEY [<using_index>]
    | UNIQUE      [<using_index>]
    | REFERENCES other_table [(colname)] [<using_index>]
        [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
    | CHECK (<check_condition>)
    | NOT NULL }

<tconstraint> ::=
  [CONSTRAINT constr_name]
    { PRIMARY KEY (<col_list>) [<using_index>]
    | UNIQUE      (<col_list>) [<using_index>]
    | FOREIGN KEY (<col_list>)
        REFERENCES other_table [(<col_list>)] [<using_index>]
        [ON DELETE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
        [ON UPDATE {NO ACTION | CASCADE | SET DEFAULT | SET NULL}]
    | CHECK (<check_condition>) }

<col_list> ::= colname [, colname ...]

<using_index> ::= USING
  [ASC[ENDING] | DESC[ENDING]] INDEX indexname

<check_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT FROM <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<check_condition>)
  | NOT <check_condition>
  | <check_condition> OR <check_condition>
  | <check_condition> AND <check_condition>

<operator> ::=
    <> | != | ^= | ~= | = | < | > | <= | >=
  | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    colname ['['array_idx [, array_idx ...]']']
  | <literal>
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <cast_type>)
  | (<select_one>)
  | func([<val> [, <val> ...]])

<cast_type> ::= <domain_or_non_array_type> | <array_datatype>

<domain_or_non_array_type> ::=
  !! See Scalar Data Types Syntax !!

<table_attrs> ::= <table_attr> [<table_attr> ...]

<table_attr> ::=
    <sql_security>
  | {ENABLE | DISABLE} PUBLICATION

<sql_security> ::= SQL SECURITY {INVOKER | DEFINER}

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

<gtt_table_attr> ::=
    <sql_security>
  | ON COMMIT {DELETE | PRESERVE} ROWS

Table 1. `CREATE TABLE` Statement Parameters
Parameter	Description
tablename	Name (identifier) for the table.The maximum length is 63 characters and must be unique in the database.
filespec	File specification (only for external tables).Full file name and path, enclosed in single quotes, correct for the local file system and located on a storage device that is physically connected to Firebird’s host computer.
colname	Name (identifier) for a column in the table.The maximum length is 63 characters and must be unique in the table.
tconstraint	Table constraint
table_attrs	Attributes of a normal table
gtt_table_attrs	Attributes of a global temporary table
datatype	SQL data type
domain_name	Domain name
start_value	The initial value of the identity column
inc_value	The increment (or step) value of the identity column, default is `1`;zero (`0`) is not allowed.
col_constraint	Column constraint
constr_name	The name (identifier) of a constraint.The maximum length is 63 characters.
other_table	The name of the table referenced by the foreign key constraint
other_col	The name of the column in other_table that is referenced by the foreign key
literal	A literal value that is allowed in the given context
context_var	Any context variable whose data type is allowed in the given context
check_condition	The condition applied to a CHECK constraint, that will resolve as either true, false or `NULL`
collation	Collation
select_one	A scalar `SELECT` statement — selecting one column and returning only one row
select_list	A `SELECT` statement selecting one column and returning zero or more rows
select_expr	A `SELECT` statement selecting one or more columns and returning zero or more rows
expression	An expression resolving to a value that is allowed in the given context
genname	Sequence (generator) name
func	Internal function or UDF

The CREATE TABLE statement creates a new table.Its name must be unique among the names of all tables, views, and stored procedures in the database.

A table must contain at least one column that is not computed, and the names of columns must be unique in the table.

A column must have either an explicit SQL data type, the name of a domain whose attributes will be copied for the column, or be defined as COMPUTED BY an expression (a calculated field).

A table may have any number of table constraints, including none.

Character Columns

You can use the CHARACTER SET clause to specify the character set for the CHAR, VARCHAR and BLOB (text subtype) types.If the character set is not specified, the default character set of the database — at time of the creation of the column — will be used.

If the database has no default character set, the NONE character set is applied.Data in any encoding can be added to such a column, but it is not possible to add this data to a column with a different encoding.No transliteration is performed between the source and destination encodings, which may result in errors.

The optional COLLATE clause allows you to specify the collation for character data types, including BLOB SUB_TYPE TEXT.If no collation is specified, the default collation for the specified character set — at time of the creation of the column — is applied.

Who Can Create a Table

The CREATE TABLE statement can be executed by:

Administrators
Users with the CREATE TABLE privilege

The user executing the CREATE TABLE statement becomes the owner of the table.