SQL Reference Guide > SQL Reference Guide > SQL Statements > CREATE TABLE
Was this helpful?
CREATE TABLE
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE TABLE statement creates a base table.
Notes:
• An X100 table cannot be created in iidbdb of imadb (system databases).
• This statement has additional considerations when used in a distributed environment. For more information, see the Star User Guide.
The CREATE TABLE statement has the following format:
[EXEC SQL] CREATE TABLE [IF NOT EXISTS] [schema.] tablename
              (column_specification {, column_specification }
              [, [CONSTRAINT constraint_name] table_constraint
              {, [CONSTRAINT constraint_name] table_constraint}])
              [with_clause]
The CREATE TABLE...AS SELECT statement (which creates a table and loads rows from another table) has the following format:
[EXEC SQL] CREATE TABLE tablename
              (column_name {, column_name}) AS
                            [WITH common_table_expression] subselect
                            {UNION [ALL]
                            subselect}
              [with_clause]
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 preexisting table may differ from that of the CREATE TABLE IF NOT EXISTS statement.
tablename
Defines the name of the new table, which must be a valid object name.
column_specification
Defines the characteristics of the column, as described in Column Specification--Define Column Characteristics.
Note:  For X100 tables, defaults, nullability, and identity columns work only for INSERT...VALUE, not INSERT SELECTs.
table_constraint
Specifies the table-level constraint as described in Column-Level Constraints and Table-Level Constraints.
with_clause
Consists of a comma-separated list of one or more of the following options, described in detail in WITH Clause for CREATE TABLE:
• LOCATION = (location_name {, location_name})
• [NO]JOURNALING
• [NO]DUPLICATES*
• PAGE_SIZE = n*
• SECURITY_AUDIT = (audit_opt {, audit_opt})
• SECURITY_AUDIT_KEY = (column)*
• STRUCTURE = structure
• NOPARTITION | PARTITION = (partitioning-scheme)
• [NO]AUTOSTRUCT*
• COMPRESSION[= ([[NO]KEY] [,[NO]DATA])] | NOCOMPRESSION*
• ALLOCATION = n*
• EXTEND = n*
• PRIORITY = n*
• ENCRYPTION=AES128|AES192|AES256, [AESKEY=hex-aes-key,] PASSPHRASE='encryption-passphrase'
• STRUCTURE = X100 | X100_ROW
• [NO]MINMAX = [(column {, column})]. Valid for X100 only.
* Not valid for X100 tables.
Additional options on the WITH clause for CREATE TABLE...AS SELECT (see WITH Clause for CREATE TABLE...AS SELECT) are as follows:
• STRUCTURE = HASH | HEAP | ISAM | BTREE
• KEY = (column_name {, column_name})
• FILLFACTOR = n
• MINPAGES = n
• MAXPAGES = n
• LEAFFILL = n
• NONLEAFFILL = n
AS [WITH common_table_expression] subselect
(Valid in CREATE TABLE AS SELECT only.)
Specifies a SELECT clause, as described in SELECT (interactive). Also see Using CREATE TABLE...AS SELECT.
Note:  Subselect cannot be used when creating a table in one or more raw locations—that is, CREATE TABLE raw_table AS SELECT... WITH LOCATION = (raw_loc).
The subselect can be preceded by a common table expression (see WITH (common_table_expression)).
Last modified date: 08/14/2024