CREATE TABLE

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition can include other attributes of the table, such as its primary key and its table space.
Restriction: This topic applies to Windows environments only.

CREATE TABLE clauses have been extended to include options for defining and dropping table check constraints, and specifying columns with user-defined defaults.

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

To execute the CREATE TABLE command, users must possess one (or more) privileges listed in at least one of the following:

  • DBADM, DBCTRL, DBMAINT or CREATETS authority for the database.
  • Overall SYSADM or SYSCTRL authority for the location.
  • CREATETAB privilege for the database that is implicitly or explicitly specified by the IN clause.

Additional privileges might be required in the following conditions:

  • The clause IN, LIKE or FOREIGN KEY is specified.
  • The data type of a column is a distinct type.
  • The table space is implicitly created.
  • A fullselect is specified.
  • A column is defined as a security label column.

If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID of the owner of the plan or package.

  • If the privilege set lacks SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table must be the same as the authorization ID of the owner of the plan or package.
  • If the privilege set includes SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table can be any authorization ID.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process.

  • If the privilege set lacks SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table must be the same as one of the authorization IDs of the process and the privilege set that is held by that authorization ID includes all privileges needed to create the table.
  • If the privilege set includes SYSADM or SYSCTRL authority, DBADM authority for the database, or DBCTRL authority for the database, the qualifier (owner) of the table can be any authorization ID.

Syntax

Bold text indicates clauses or options that are supported only syntactically. 

CREATE TABLE table-name
{ 
    (column-definition 
        [unique-constraint] 
        [referential-constraint] 
        [select-command] 
        [check-constraint] [,...]) 
     | LIKE {table-name | view-name} [INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
     | materialized-query-definition
}
    [WITH RESTRICT ON DROP]
    [{IN [database-name.]tablespace-name 
        | IN DATABASE database-name}]
    [partitioning-clause]
    [EDITPROC program-name]
    [VALIDPROC program-name]
    [AUDIT {NONE | CHANGES | ALL}]
    [OBID integer]
    [DATA CAPTURE {NONE | CHANGES}]
    [WITH RESTRICT ON DROP]
    [CCSID {ASCII | EBCDIC | UNICODE}]
     [{VOLATILE | NOT VOLATILE} [CARDINALITY]]

unique-constraint: 

{UNIQUE | PRIMARY KEY}(column-name[,...])

referential-constraint: 

FOREIGN KEY [constraint-name] (column-name[,...]) 
    references-clause

references-clause: 

REFERENCES referenced-table
    [column-name ,...]
    [ON DELETE {RESTRICT | NO ACTION | CASCADE | SET NULL}]
    [ENFORCED | NOT ENFORCED]
    [ENABLE QUERY OPTIMIZATION]

check-constraint: 

[CONSTRAINT constraint-name] CHECK (check-condition)

column-definition: 

column-name data-type 
    [NOT NULL]
    [column-constraint]
    [{UNIQUE | PRIMARY KEY}] 
    [FOR {BIT | SBCS | MIXED} DATA] 
    [default-clause] 
    [GENERATED {ALWAYS | BY DEFAULT} [as-identity-clause]
    [references-clause] 
    [check-constraint] 
    [FIELDPROC program-name [(constant[,...])]]
    [AS SECURITY LABEL]

default-clause: 

[WITH] DEFAULT 
[{constant | USER | CURRENT SQLID | NULL | cast-function-name ({constant | USER | CURRENT SQLID | NULL})}]

data-type: 

{built-in-data-type | distinct-type-name}

built-in-data-type: 

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) } 
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] | 
{DATE | TIME | TIMESTAMP} |
ROWID

as-identity-clause: 

AS IDENTITY
[({START WITH {numeric-constant | 1} 
| INCREMENT BY {numeric-constant | 1} 
| MINVALUE numeric-constant | NOMINVALUE
| MAXVALUE numeric-constant | NOMAXVALUE
| CYCLE | NOCYCLE
| ORDER | NO ORDER
| {CACHE 20 | NO CACHE | CACHE integer}}][,..])]

partitioning-clause: 

PARTITION BY [RANGE] (partition-expression[,...]) (partition-element[,...])

partition-expresion: 

column-name [NULLS LAST] [ASC | DESC]

partition-element: 

PARTITION integer ENDING [AT] (constant[,...]) [INCLUSIVE]

materialized-query-definition: 

[(column-name[,...])] AS (fullselect)
{WITH NO DATA [copy-options] | refreshable-table-options}

copy-options: 

[EXCLUDING IDENTITY [COLUMN ATTRIBUTES] | 
INCLUDING IDENTITY [COLUMN ATTRIBUTES]]
[EXCLUDING [COLUMN] DEFAULTS | INCLUDING [COLUMN] DEFAULTS | USING TYPE DEFAULTS]

refreshable-table-options: 

DATA INITIALLY DEFERRED REFRESH DEFERRED
[[MAINTAINED BY SYSTEM | MAINTAINED BY USER] 
[ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] ...]

Parameters:

table-name
Any long identifier naming the table. The name must uniquely identify the table, and must not match any other table, view, alias, or synonym name existing within the current location. If qualified, the table name consists of a two-part or three-part name. If a three-part name is specified, the first part must match the value of the CURRENT SERVER special register -- that is, the current location. In any case, the AuthID qualifying the table name is the table owner.
column-name
Any long identifier naming the column. Each column in a table must have an unqualified column name, unique within the table. Dependent tables (tables containing a foreign key referencing a primary key in another table) can have up to 749 columns named, while nondependent tables (tables containing the primary key) may have 750 named columns. Column names are optional only when the select-command statement is included, in which case the column names from the source table are used.
constant
FIELDPROC clause: a parameter passed to the field procedure when it is invoked during execution of the CREATE TABLE statement. A parameter list is optional, and the number of parameters and the data type of each are determined by the field procedure. The maximum length of the parameter list is 254 bytes, including commas but excluding insignificant blanks and the delimiting parentheses.

[WITH] DEFAULT clause: names the default value for the column. The value of the constant must conform to the rules for assigning that value to the column. A default value can be specified for a column with a data type of LONG VARCHAR or LONG VARGRAPHIC and a maximum length of 254 bytes.

A character or graphic string constant must be short enough so that its UTF-8 representation requires no more than 1536. In addition, a hexadecimal graphic string constant (GX) cannot be specified.