FirebirdSQL logo

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.