Background documentationCREATE TABLE Statement (create_table_statement) Locate this document in the navigation structure

 

The CREATE TABLE statement (create_table_statement) defines a base table. There are three syntactic options for creating a table.

Structure

Syntax Syntax

  1. <create_table_statement> ::=
  CREATE TABLE <table_name>
    (<table_description_element> [,<table_description_element>,...])
    [IGNORE ROLLBACK]
    [<sample_definition>]
    [CACHE] [NOCACHE]
| CREATE TABLE <table_name>
    [(<table_description_element>,...)]
    [IGNORE ROLLBACK]
    [<sample_definition>]
    AS <query_expression>
    [<duplicates_clause>]
| CREATE TABLE <table_name> LIKE <table_name>
    [IGNORE ROLLBACK]
    
<table_description_element> ::=
  <column_definition>
| <constraint_definition>
| <key_definition>
| <referential_constraint_definition>
| <unique_definition>
End of the code.
Examples

SQL Tutorial, Tables, Primary Key, Constraints, Foreign Key Dependencies Between Tables, Indexes, Domains

Explanation

Executing a CREATE TABLE statement causes data that describes the table (or base table) to be stored in the database catalog. This data is called metadata.

A CREATE TABLE statement cannot contain more than one key definition (key_definition). If the name of the schema is not specified in the table name (table_name), the current schema is assumed implicitly. The table name must not be identical to the name of an existing table in the schema.

The current database user must have the CREATEIN privilege for the schema specified either implicitly or explicitly in the table name. The current database user becomes the owner of the created table. The user obtains the INSERT, UPDATE, DELETE and SELECT privileges for this table. If the table is not a temporary table, the owner is also granted the INDEX, REFERENCES, and ALTER privileges.

Schema Assignment of a Table

  • The schema name is specified before the table name: temporary tables are identified by the schema TEMP before their name. If a table belongs to a schema other than TEMP, the current user must have the CREATEIN privilege for the specified schema.

  • The schema name is not specified: the table belongs to the current schema. This is either the schema that bears the name of the current database user or the schema specified in the SET CURRENT SCHEMA statement.

CREATE TABLE <table_name> (<table_description_element> [,<table_description_element>]) [IGNORE ROLLBACK] [<sample_definition>] [CACHE] [NOCACHE]

If this syntactic option for creating table is specified, the CREATE TABLE statement must contain at least one column definition (column_definition).

CACHE

The specification of CACHE for a table means that data from the table (row values, LOB values, index data) that has been read into the data cache once remains there, as far as possible.

An area is reserved in the data cache for all tables with the CACHE option. The size of this reserved area is defined by the database parameter DataCachePinAreaSize. Data is only replaced in this reserved data cache area if inserting a new data page into the reserved area would cause the permitted size of this reserved area to be exceeded.

The CACHE option is particularly suited for tables that are accessed very frequently.

If CACHE is specified, you cannot specify NOCACHE.

NOCACHE

The specification of NOCACHE for a table means that data from the table (row values, LOB values, index data) that is read into the data cache is removed from the data cache very quickly.

The NOCACHE option is suitable for tables for which a long retention of the data in the data cache is not useful, either because the table is very seldom accessed or because there is no locality of accesses.

If NOCACHE is specified, you cannot specify CACHE.

CREATE TABLE <table_name> [(<table_description_element>,...)] [IGNORE ROLLBACK] [<sample_definition>] AS <query_expression> [<duplicates_clause>]

If a QUERY expression is specified, a base table is defined with the same structure as the result table defined by the QUERY expression.

If column definitions are specified, the column definition may only consist of a column name and the number of column definitions must be equal to the number of columns in the result table generated by the QUERY expression. The data type of the ith column in the base table is identical to that of the ith column in the result table generated by the QUERY expression. The result table may also contain LOB columns. If no column definitions are specified, the column names of the result table are used. The rows of the result table are implicitly inserted in the generated base table.

The QUERY expression is subject to certain restrictions (see also INSERT Statement).

The DUPLICATES clause can be used to determine how key collisions are handled.

CREATE TABLE <table_name> LIKE <table_name> [IGNORE ROLLBACK]

If LIKE <table_name> is specified, an empty base table is created which, from the point of view of the current database user, has the same structure as the source table, that is, it has all the columns with the same column names and definitions as the source table. This view does not necessarily have to be identical to the actual structure of the source table, since the database user may not know all the columns because of privilege limitations.

The specified table must be either a base table, a view table, or a synonym. The database user must have at least one privilege for this table. The current database user is the owner of the base table.

If all the key columns of the table specified after LIKE are contained in the base table, they form the key columns in this table. Otherwise, the database system implicitly inserts a key column SYSKEY CHAR(8) BYTE which then represents the key for the base table.

DEFAULT specifications or CONSTRAINT definitions for columns that are copied to the base table also apply to the new base table.

IGNORE ROLLBACK

IGNORE ROLLBACK is optional and can only be specified for temporary tables. Temporary tables with this characteristic are not affected by the transaction mechanism; in other words, changes affecting these tables are not reversed by rolling back a transaction.

More Information