All Products
Search
Document Center

CREATE TABLE

Last Updated: Jun 18, 2021

Description

The CREATE TABLE statement creates a table in a database.

Syntax

CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
      (table_definition_list) [table_option_list] [partition_option] [AS] select;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | INDEX [index_name] index_desc
    | [CONSTRAINT [constraint_name]] [PRIMARY KEY|UNIQUE]  (column_desc_list) [USING INDEX index_option_list]
    | [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constranit_state
    | [CONSTRAINT [constraint_name]] CHECK(expression) constranit_state
    

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
    column_name data_type
    [VISIBLE|INVISIBLE]
    {
    [DEFAULT expression]
    [NULL | NOT NULL] 
    [CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
    [CONSTRAINT [constraint_name] CHECK(expression) constranit_state]
    [CONSTRAINT [constraint_name] references_clause
    |
    [GENERATED ALWAYS] AS (expression) [VIRTUAL]
    [NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [COMMENT string]
    }
     
references_clause:
     REFERENCES table_name (column_name, column_name ...) [ON DELETE {CASCADE|SET NULL}]] 

constranit_state:
    [RELY|NORELY] [USING INDEX index_option_list] [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]

index_desc:
   (column_desc_list) [index_option_list]

column_desc_list:
    column_desc [, column_desc ...]

column_desc:
     column_name [ASC | DESC][NULL LAST|NULL FIRST]

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment

table_option_list:
    table_option [ table_option ...]

table_option:
    primary_zone
    | replica_num
    | table_tablegroup
    | block_size
    | compression
    | comment
    | DUPLICATE_SCOPE [=] "none|zone|region|cluster"
    | LOCALITY [=] "locality description"
    | ENABLE ROW MOVEMENT
    | DISABLE ROW MOVEMENT
    | physical_attribute

physical_attribute_list:
    physical_attribute [physical_attribute]

physical_attribute:
    PCTFREE [=] num
    | PCTUSED num
    | INITRANS num
    | MAXTRANS num
    | STORAGE(storage_option [storage_option] ...)
    | TABLESPACE tablespace
    

compression:
    NOCOMPRESS
    | COMPRESS { BASIC | FOR OLTP | FOR QUERY [LOW|HIGH]  | FOR ARCHIVE [LOW|HIGH]} 

storage_option:
    INITIAL_ num [K|M|G|T|P|E]
    | NEXT num [K|M|G|T|P|E]
    | MINEXTENTS num [K|M|G|T|P|E]
    | MAXEXTENTS num [K|M|G|T|P|E]

partition_option:
      PARTITION BY HASH(column_name_list)
      [subpartition_option] PARTITIONS partition_count [TABLESPACE tablespace] [compression]
    | PARTITION BY RANGE (column_name_list)
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST (column_name_list)
      [subpartition_option] (list_partition_list)

subpartition_option:
      SUBPARTITION BY HASH (column_name_list) SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE (column_name_list) SUBPARTITION TEMPLATE
      (range_subpartition_list)
    | SUBPARTITION BY LIST (column_name_list) SUBPARTITION TEMPLATE (list_subpartition_list)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION [partition_name]
    VALUES LESS THAN {(expression_list) | MAXVALUE} [ID = num] [physical_attribute_list] [compression]

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE} [physical_attribute_list]

list_partition_list:
    list_partition [, list_partition] ...

list_partition:
    PARTITION [partition_name] VALUES (DEFAULT|expression_list) [ID num] [physical_attribute_list] [compression]

list_subpartition_list:
    list_subpartition [, list_subpartition] ...

list_subpartition:
    SUBPARTITION [partition_name] VALUES (DEFAULT|expression_list) [physical_attribute_list]

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

on_commit_option:
    ON COMMIT DELETE ROWS
    | ON COMMIT PRESERVE ROWS

Parameter description

Parameter

Description

DUPLICATE_SCOPE

Specifies the attributes of the replicated table. Valid values:

  • none: indicates that the table is a standard table.

  • zone: indicates that the table is a replicated table. The leader needs to replicate transactions to all the F replicas and R replicas of the current zone.

  • region: indicates that the table is a replicated table. The leader needs to replicate transactions to all the F replicas and R replicas of the current region.

  • cluster: indicates that the table is a replicated table. The leader needs to replicate transactions to all the F replicas and R replicas of the cluster.

If DUPLICATE_SCOPE is not specified, the default value is none.

BLOCK_SIZE

Specifies the micro-block size of the table.

COMPRESSION

Specifies the flat or encoding storage format and the compression method. The following correspondence relationships are available:

  • nocompress: flat format, none compression

  • compress [basic]: flat format, lz4_1.0 compression

  • compress for oltp: flat format, zstd_1.0 compression

  • query [low|high]: encoding format, lz4_1.0 compression

  • archive [low|high]: encoding format, zstd_1.0 compression

primary_zone

Specifies the primary zone where the leader replica resides.

replica_num

Specifies the number of replicas.

table_tablegroup

Specifies the tablegroup to which the table belongs.

comment

The comment.

LOCALITY

Describes the distribution of replicas among zones. For example, F@z1,F@z2,F@z3,R@z4 indicates that z1, z2, and z3 are full-featured replicas and z4 is a read-only replica.

physical_attribute

PCTFREE: specifies the percentage of reserved macro block space.

Other attributes: Attributes such as STORAGE and TABLESPACE are only for syntax compatibility to facilitate migration and do not take effect.

ENABLE/DISABLE ROW MOVEMENT

Specifies whether to allow data to be moved between partitions.

ON COMMIT DELETE ROWS

Transaction-level temporary tables: The data is deleted on commit.

ON COMMIT PRESERVE ROWS

Session-level temporary tables: The data is deleted when the session ends.

Examples

  • Create a database table.

CREATE TABLE test (c1 int primary key, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';
  • Create a replicated table.

CREATE TABLE item() locality = 'F,R{all_server}@hz1, F,R{all_server}@hz2, 
F,R{all_server}@hz3' DUPLICATE_SCOPE="cluster"
  • Create a table that has indexes.

create table t1 (c1 int primary key, c2 int, c3 int, index i1 (c2));
  • Create a table that has eight hash partitions.

create table t1 (c1 int primary key, c2 int) partition by hash(c1) partitions 8;
  • Create a table that has range partitions and hash subpartitions.

 create table t1 (c1 int, c2 int, c3 int)
     partition by range(c1) subpartition by hash(c2) subpartitions 5
     (partition p0 values less than(0), partition p1 values less than(100));
  • Enable encoding and zstd compression. Set the percentage of reserved macro block space to 5%.

create table t1 (c1 int, c2 int, c3 varchar(64))
  COMPRESS FOR ARCHIVE
  PCTFREE 5;
  • Create a transaction-level temporary table.

create global temporary table t1 (c1 int) on commit delete rows ;
  • Create a table that has a constraint.

create table t1 (c1 int, c2 int, c3 int, CONSTRAINT equal_check CHECK(c2 = c3 * 2) ENABLE VALIDATE);