4. SQL Statements : CREATE TABLE
 
Share this page                  
CREATE TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE TABLE statement creates a base table.
Note:  A Vector table cannot be created in either iidbdb or imadb (system databases).
This statement has the following format:
CREATE TABLE [IF NOT EXISTS] [schema.] table_name
       (column_specification {, column_specification })
       [table_constraint {, table_constraint}]
       [with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and loads rows from another table) has the following format:
CREATE TABLE table_name
        (column_name {, column_name}) AS
                      [WITH common_table_expressionsubselect
                      {UNION [ALL]
                      subselect}
        [with_clause]
where:
IF NOT EXISTS
Creates the table if it does not exist and returns without error if the table already exists.
Note:  Use this option with caution. The table definition of a pre-existing table may differ with that of the CREATE TABLE IF NOT EXISTS statement.
table_name
Defines the name of the new table. It must be a valid object name.
column_specification
Defines the characteristics of the column, as described in Column Specification.
Note:  For Vector tables, defaults, nullability, and identity columns work only for INSERT...VALUE, not INSERT SELECTs.
table_constraint
Specifies the table-level constraint as described in Table-level and Column-level Constraints.
with_clause
Specifies optional WITH clause options separated by a comma, as described in WITH Clause.