CREATE TABLE

Purpose

You can use this statement to create a table in the database.

Syntax

CREATE [hint_options] TABLE [IF NOT EXISTS] table_name
      (table_definition_list) [table_option_list] [partition_option] [IGNORE | REPLACE] [AS] select;

CREATE TABLE [IF NOT EXISTS] table_name
      LIKE table_name;

table_definition_list:
    table_definition [, table_definition ...]

table_definition:
      column_definition
    | [CONSTRAINT [constraint_name]] PRIMARY KEY index_desc
    | [CONSTRAINT [constraint_name]] UNIQUE {INDEX | KEY} [index_name] index_desc
    | [CONSTRAINT [constraint_name]] FOREIGN KEY [index_name] (column_name, ...) reference_definition
    | {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_optionn_list]
    | [CONSTRAINT [constraint_name]] CHECK(expression) [NOT ENFORCED | ENFORCED]

column_definition_list:
    column_definition [, column_definition ...]

column_definition:
     column_name data_type
         [DEFAULT const_value] [AUTO_INCREMENT]
         [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
         [COLUMN_FORMAT {FIXED | DYNAMIC | DEFAULT}]
         [STORAGE {DISK | MEMORY}]
         [reference_definition]
   | column_name data_type
         [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
         [opt_generated_column_attribute]
         [reference_definition]

index_desc:
    (column_desc_list) [index_type] [index_option_list]

reference_definition:
    REFERENCES tbl_name (key_part,...)
    [match_action] [opt_reference_option_list]

match_action:
    MATCH {SIMPLE | FULL | PARTIAL}

opt_reference_option_list:
    reference_option [,reference_option ...]

reference_option:
    ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option_list:
    index_option [ index_option ...]

index_option:
      [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name_list)
    | comment
    | KEY_BLOCK_SIZE [=] INT_VALUE
    | WITH PARSER parser_name

table_option_list:
    table_option [ table_option ...]

table_option:
      [DEFAULT] {CHARSET | CHARACTER SET} [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | table_tablegroup
    | block_size
    | lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | TTL (ttl_definition) 
    | ROW_FORMAT [=] REDUNDANT|COMPACT|DYNAMIC|COMPRESSED|DEFAULT
    | PCTFREE [=] num
    | parallel_clause
    | DUPLICATE_SCOPE [=] 'none|cluster'
    | KEY_BLOCK_SIZE [=] INT_VALUE
    | TABLE_MODE [=] 'table_mode_value'
    | auto_increment_cache_size [=] INT_VALUE
    | AVG_ROW_LENGTH [=] value
    | CHECKSUM [=] {0 | 1}
    | CONNECTION [=] 'connect_string'
    | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory'
    | DELAY_KEY_WRITE [=] {0 | 1}
    | ENCRYPTION [=] {'Y' | 'N'}
    | ENGINE [=] engine_name
    | INSERT_METHOD [=] { NO | FIRST | LAST}
    | MAX_ROWS [=] value
    | MIN_ROWS [=] value
    | PACK_KEYS [=] {0 | 1 | DEFAULT}
    | PASSWORD [=] 'string'
    | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1}
    | STATS_PERSISTENT [=] {DEFAULT | 0 | 1}
    | STATS_SAMPLE_PAGES [=] value
    | UNION [=] (tbl_name[,tbl_name]...)

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name_list])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] (range_partition_list)
    | PARTITION BY LIST {(expression) | COLUMNS (column_name_list)}
      [subpartition_option] PARTITIONS partition_count

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name_list)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name_list)}
      (range_subpartition_list)
    | SUBPARTITION BY LIST(expression)

range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
    range_subpartition [, range_subpartition ...]

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

expression_list:
    expression [, expression ...]

column_name_list:
    column_name [, column_name ...]

partition_name_list:
    partition_name [, partition_name ...]

partition_count | subpartition_count:
    INT_VALUE

Parameters

reference_definition

• REFERENCES tbl_name (key_part,...): the parent table and column with which the foreign key is associated.

  • tbl_name: the name of the referenced table, namely, the name of the parent table with which the foreign key is associated.
  • key_part: the name of the referenced column in the parent table, namely, the column with which the foreign key is associated. In most cases, it is the primary key column or unique key column.

• match_action: the matching method used for the foreign key check. Valid values:

  • MATCH SIMPLE: If the value of any column in the foreign key is NULL, the foreign key check is not performed. This is the default value.
  • MATCH FULL: If the referencing foreign key columns are deemed to fully match the primary key columns, all foreign key columns must not contain NULL values.
  • MATCH PARTIAL: If the referencing foreign key columns are deemed to partially match the primary key columns, some foreign key columns can have NULL values. In this case, it is also considered that the FOREIGN KEY constraint is met.

• opt_reference_option_list: the response action of the foreign key table when a value in the parent table is dropped or updated. The following options are supported:

  • ON DELETE: the operation to perform when a row in the parent table is dropped.
  • ON UPDATE: the operation to perform when a row in the parent table is updated.

  The preceding options can be followed with the following actions:

  • RESTRICT: prevents rows in the parent table from being dropped or updated.
  • CASCADE: drops or updates the row in the foreign key table when a row in the parent table is dropped or updated.
  • SET NULL: sets the values of the corresponding primary key columns to NULL, if NULL values are allowed.
  • NO ACTION: similar to RESTRICT.
  • SET DEFAULT: sets the corresponding primary key columns to default values, if they have default values.

table_mode_value

• NORMAL: This is the default value. In this mode, the probability of triggering a major compaction after a minor compaction is extremely low.
• QUEUING: In this mode, the probability of triggering a major compaction after a minor compaction is low.
• MODERATE: In this mode, the probability of triggering a major compaction after a minor compaction is moderate.
• SUPER: In this mode, the probability of triggering a major compaction after a minor compaction is high.
• EXTREME: In this mode, the probability of triggering a major compaction after a minor compaction is very high.

For more information about major compactions, see Adaptive major compactions.

Examples

• Create a database table.

  obclient> CREATE TABLE tbl1 (c1 INT PRIMARY KEY, c2 VARCHAR(50));
  Query OK, 0 rows affected
  

• Create an indexed table.

  obclient> CREATE TABLE tbl2 (c1 INT PRIMARY KEY, c2 INT, c3 INT, INDEX i1 (c2));
  Query OK, 0 rows affected
  

• Create a table named tbl3 with a function-based index.

  obclient> CREATE TABLE tbl3 (c1 INT, c2 INT, INDEX i1 ((c1+1)), UNIQUE KEY ((c1+c2)));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl3;
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                     |
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl3    | CREATE TABLE `tbl3` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  UNIQUE KEY `functional_index` ((`c1` + `c2`)) BLOCK_SIZE 16384 LOCAL,
  KEY `i1` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  

• Create a HASH-partitioned table with eight partitions.

  obclient> CREATE TABLE tbl4 (c1 INT PRIMARY KEY, c2 INT) PARTITION BY HASH(c1) PARTITIONS 8;
  Query OK, 0 rows affected
  

• Create a RANGE-KEY-subpartitioned table.

  obclient> CREATE TABLE tbl5 (c1 INT, c2 INT, c3 INT) PARTITION BY RANGE(c1)
       SUBPARTITION BY KEY(c2, c3) SUBPARTITIONS 5
       (PARTITION p0 VALUES LESS THAN(0), PARTITION p1 VALUES LESS THAN(100));
  Query OK, 0 rows affected
  

• Create a table that uses gbk for one column and utf8 for the other column.

  obclient> CREATE TABLE tbl6 (c1 VARCHAR(10),
                   c2 VARCHAR(10) CHARSET GBK COLLATE gbk_bin)
               DEFAULT CHARSET utf8 COLLATE utf8mb4_general_ci;
  Query OK, 0 rows affected
  

• Enable encoding and zstd compression for the table and reserve 5% of space for macroblocks.

  obclient> CREATE TABLE tbl7 (c1 INT, c2 INT, c3 VARCHAR(64))
        COMPRESSION 'zstd_1.0'
        ROW_FORMAT DYNAMIC
        PCTFREE 5;
  Query OK, 0 rows affected
  

• Create a table named tbl8 and set the DOP to 3.

  obclient> CREATE TABLE tbl8(c1 INT PRIMARY KEY, c2 INT) PARALLEL 3;
  Query OK, 0 rows affected
  

• Use the auto-increment column as the partitioning key.

  obclient> CREATE TABLE tbl9(inv_id BIGINT NOT NULL AUTO_INCREMENT,c1 BIGINT,
  PRIMARY KEY (inv_id) ) PARTITION BY HASH(inv_id) PARTITIONS 8;
  Query OK, 0 rows affected
  

• Specify a foreign key for the ref_t2 table. Specify to execute the SET NULL action when an UPDATE operation affects a key value in the parent table that has matching rows in the child table.

  obclient> CREATE TABLE ref_t1(c1 INT PRIMARY KEY, C2 INT);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE ref_t2(c1 INT PRIMARY KEY, C2 INT,FOREIGN KEY(c2) REFERENCES ref_t1(c1) ON UPDATE SET NULL);
  Query OK, 0 rows affected
  

• Create a table named tbl10 with the CHECK constraint and view the constraint information.

  obclient> CREATE TABLE tbl10 (col1 INT, col2 INT, col3 INT, CONSTRAINT equal_check1 CHECK(col1 = col3 * 2));
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl10;
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                               |
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl10  | CREATE TABLE `tbl10` (
    `col1` int(11) DEFAULT NULL,
    `col2` int(11) DEFAULT NULL,
    `col3` int(11) DEFAULT NULL,
    CONSTRAINT `equal_check1` CHECK ((`col1` = (`col3` * 2)))
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• Create a table named tbl11 based on the tbl10 table and view the table information.

  obclient> CREATE TABLE tbl11 LIKE tbl10;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11  | CREATE TABLE `tbl11` (
    `col1` int(11) DEFAULT NULL,
    `col2` int(11) DEFAULT NULL,
    `col3` int(11) DEFAULT NULL,
    CONSTRAINT `tbl11_OBCHECK_1650793233327894` CHECK ((`col1` = (`col3` * 2)))
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• Create a cluster-level replicated table named dup_t1.

  1. Log in to the sys tenant and create a unit.

     obclient> CREATE RESOURCE UNIT IF NOT EXISTS 2c5g MAX_CPU 2, MEMORY_SIZE '5G';
     Query OK, 0 rows affected
     

  2. Create a resource pool and set UNIT_NUM to 2.

     obclient> CREATE RESOURCE POOL tenant_pool UNIT = '2c5g', UNIT_NUM = 2, ZONE_LIST = ('z1', 'z2', 'z3');
     Query OK, 0 rows affected
     

  3. Create a user tenant named obmysql and specify the locality.

     obclient> CREATE TENANT obmysql resource_pool_list=('tenant_pool'), LOCALITY = "F@z1, F@z2, R@z3", PRIMARY_ZONE = "z1" SET ob_tcp_invited_nodes='%';
     Query OK, 0 rows affected
     

  4. Log in to the obmysql user tenant created in step 3 and create a replicated table.

     obclient> USE test;
     Database changed
     obclient> CREATE TABLE dup_t1(c1 INT) DUPLICATE_SCOPE = 'cluster';
     Query OK, 0 rows affected
     

  5. (Optional) View the broadcast log stream. The replicated table is created on this log stream.

     obclient> SELECT * FROM oceanbase.DBA_OB_LS WHERE FLAG LIKE "%DUPLICATE%";
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
     | LS_ID | STATUS | PRIMARY_ZONE | UNIT_GROUP_ID | LS_GROUP_ID | CREATE_SCN          | DROP_SCN | SYNC_SCN            | READABLE_SCN        | FLAG      |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
     |  1003 | NORMAL | z1;z2        |             0 |           0 | 1683267390195713284 |     NULL | 1683337744205408139 | 1683337744205408139 | DUPLICATE |
     +-------+--------+--------------+---------------+-------------+---------------------+----------+---------------------+---------------------+-----------+
     1 row in set
     
     
     

  6. (Optional) View the replica distribution of the replicated table in the sys tenant. The REPLICA_TYPE column indicates the replica type.

     obclient> SELECT * FROM oceanbase.CDB_OB_TABLE_LOCATIONS WHERE TABLE_NAME = "dup_t1";
     +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
     | TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | TABLE_TYPE | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | DATA_TABLE_ID | TABLET_ID | LS_ID | ZONE | SVR_IP         | SVR_PORT | ROLE     | REPLICA_TYPE | DUPLICATE_SCOPE |
     +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx |    36125 | LEADER   | FULL         | CLUSTER         |
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z1   | 11.xxx.xxx.xxx  |    36124 | FOLLOWER | READONLY     | CLUSTER         |
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36127 | FOLLOWER | FULL         | CLUSTER         |
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z2   | 11.xxx.xxx.xxx |    36126 | FOLLOWER | READONLY     | CLUSTER         |
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx |    36128 | FOLLOWER | READONLY     | CLUSTER         |
     |      1002 | test          | dup_t1     |   500002 | USER TABLE | NULL           | NULL              | NULL       |          NULL |    200001 |  1003 | z3   | 11.xxx.xxx.xxx  |    36129 | FOLLOWER | READONLY     | CLUSTER         |
     +-----------+---------------+------------+----------+------------+----------------+-------------------+------------+---------------+-----------+-------+------+----------------+----------+----------+--------------+-----------------+
     6 rows in set
     

  7. Insert data into, read data from, and write data to the replicated table. If you connect to the database by using OceanBase Database Proxy (ODP), the read request may be routed to any OBServer node. If you directly connect to an OBServer node, the read request is executed on the connected OBServer node as long as the local replica is readable.

     obclient> INSERT INTO dup_t1 VALUES(1);
     Query OK, 1 row affected
     
     obclient> SELECT * FROM dup_t1;
     +------+
     | c1   |
     +------+
     |    1 |
     +------+
     1 row in set
     

• Create a table named tbl1 with a col1 column of the integer type, and set the DOP to 5. Specify that the data of the tbl1 table is sourced from the results of querying the tbl2 table.

  CREATE /*+ parallel(5) */ TABLE tbl1 (col1 INT) AS SELECT col1 FROM tbl2;