Create a table

This topic describes the definition of tables, as well as the prerequisites, considerations, and examples for creating a table by using an SQL statement.

About tables

A table is a collection of two-dimensional arrays used to represent and store relationships between data objects. Through the proper design and use of database tables, data reliability, consistency, and query performance can be enhanced, effectively managing and utilizing the data within the database.

Prerequisites

Before you create a table, make sure that:

• You have deployed an OceanBase cluster and created an Oracle-compatible tenant.

• You have connected to the Oracle-compatible tenant of OceanBase Cloud.

• You have the CREATE TABLE privilege. If you do not have the required privileges, contact the administrator to obtain the privileges.

Create a table by using a statement

Use the CREATE TABLE statement to create a table.

Define the table name

When you create a table, you must define a name for it. Observe the following requirements when you define a table name:

• In the Oracle compatible mode of OceanBase Cloud, the name of each table must be unique in the schema.

• Use table names with meanings and avoid such names as table1.

Example 1: Create a table that stores order information.

obclient [SYS]> CREATE TABLE ordr (c1 NUMBER, c2 VARCHAR2(50));
Query OK, 0 rows affected

Define columns

​In a database, a column stores the values of specific attribute. The name of the attribute is used as the column name. Apart from the column name, information about a column also includes the data type and maximum length (precision).

Observe the following requirements when you define columns for a table:

• Select a proper data type for the data to be stored in columns.

• For string data, we recommend that you use a variable-length string data type and specify the maximum length. Make sure that the maximum length specified is greater than the maximum number of characters to store. This is to avoid character truncation when the maximum length is exceeded.

• Determine whether to define the PRIMARY KEY constraint on specific columns based on the requirements for the PRIMARY KEY constraint.

• Determine whether to define other constraints on specific columns based on the requirements for other constraints.

• When a column has the NOT NULL constraint, we recommend that you set a default value for the column. If the column is of a date or time data type, you can set the default value to the current time of the database.

Define a primary key column

A PRIMARY KEY constraint imposes a primary key value rule on a key, which can be a column or set of columns. This rule ensures that each data row in a table can be uniquely identified by a key value. Only one PRIMARY KEY constraint can be defined on each database table. The values of the columns that make up this constraint (one or more columns) can serve as unique identifiers for each row, effectively naming each data row with this primary key value.

When a table with a primary key is created, a globally unique index is created for the primary key to quickly locate rows through the primary key.

To set a column as a primary key column, append the PRIMARY KEY keyword to the definition of the column. To define a PRIMARY KEY constraint on multiple columns, add the PRIMARY KEY constraint after the list of all target columns in the CREATE TABLE statement.

Observe the following requirements when you define a primary key column:

• We recommend that you define a primary key for each table. Each database table can have one primary key column set.

  Although you are not forced to define a primary key for a table in OceanBase Cloud, we recommend that you do so to ensure that each row can be uniquely identified and that no duplicate rows exist in the table. If no existing columns can be used as the primary key, you can add a numeric column as the primary key and use a sequence of the Oracle-compatible tenant to generate sequential values for this column.

  If you do not define a primary key column for a table when you create the table, you can add a primary key column to the table later.

• The values of the primary key column set are unique in the table.

• A primary key can contain at most 64 columns, and the total length of primary key data cannot exceed 16 KB.

• A primary key cannot contain a NULL or empty value.

• We recommend that you explicitly specify the name for a PRIMARY KEY constraint when you define the constraint on multiple columns. For example, you can name the PRIMARY KEY constraint "PK_xxx".

Example 2: Define a PRIMARY KEY constraint on multiple columns.

obclient [SYS]> CREATE TABLE ordr (c1 NUMBER, c2 VARCHAR2(50), CONSTRAINT PK_c1_c2 PRIMARY KEY(c1, c2));
Query OK, 0 rows affected

obclient [SYS]> desc ordr;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| C1    | NUMBER       | NO   | PRI | NULL    | NULL  |
| C2    | VARCHAR2(50) | NO   | PRI | NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

In this example, columns c1 and c2 are defined as primary key columns, and the constraint name is PK_c1_c2. Values in the c1 and c2 columns cannot be NULL or duplicate.

After you define a PRIMARY KEY constraint, you can change or drop it.

Define other column constraints

In addition to the PRIMARY KEY constraint, OceanBase Cloud also supports NOT NULL, UNIQUE, FOREIGN KEY, and CHECK constraints. The constraints can simplify table queries, improve query performance, and ensure semantic validity of data.

Constraints of different types are described as follows:

• NOT NULL: The constrained column cannot contain NULL values.

  For a column with the NOT NULL constraint, you must specify the value in an INSERT statement unless you have defined a default value for the column.

• UNIQUE: The constrained column cannot contain duplicate values, but can contain more than one NULL value.

• FOREIGN KEY: The values in the constrained column come from a primary key column of another table.

  If you do not specify the foreign key name when you create a FOREIGN KEY constraint, the system automatically assigns a constraint name in the format of table name_OBFK_creation timestamp, such as t1_OBFK_1627747200000000.

• CHECK: The constrained column must meet specified conditions.

  You can define one or more CHECK constraints for a single column so that only specified values are allowed for the column. You can also define table-level CHECK constraints to apply the constraints to multiple columns. When you modify a table name, the CHECK constraint name remains unchanged. When you drop a table, the CHECK constraints applied to the table are also dropped.

  If you do not specify the constraint name when you create a CHECK constraint, the system automatically assigns a constraint name in the format of table name_OBCHECK_creation timestamp, such as t1_OBCHECK_1629350823880271.

To constrain a single column, add a constraint keyword to the definition of the column. To constrain multiple columns, add the definition of the constraint to the end of the list of all columns in the CREATE TABLE statement.

Observe the following requirements when you define other column constraints:

• We recommend that you add the NOT NULL constraint to columns that do not contain NULL values.

• If you want to reference a value from another table, use the FOREIGN KEY constraint.

• Composite primary keys cannot be used as foreign keys.

• If you want to avoid duplicate values in a column, use the UNIQUE constraint.

• We recommend that you specify a name for each constraint, except NOT NULL. For example, you can name a UNIQUE constraint "UNI_xxx" and a FOREIGN KEY constraint "FK_xxx".

Example 3: Create a table named tbl1 and set a NOT NULL constraint on the col1 column.

obclient [SYS]> CREATE TABLE tbl1(col1 NUMBER NOT NULL,col2 VARCHAR2(50));
Query OK, 0 rows affected
  
obclient [SYS]> DESC tbl1;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| COL1  | NUMBER       | NO   | NULL | NULL   | NULL  |
| COL2  | VARCHAR2(50) | YES  | NULL | NULL   | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

In this example, values subsequently inserted into the col1 column cannot be NULL.

Example 4: Create a table named tbl2 and define a UNIQUE constraint on the col1 column.

obclient [SYS]> CREATE TABLE tbl2(col1 NUMBER UNIQUE,col2 VARCHAR2(50));
Query OK, 0 rows affected

obclient [SYS]> DESC tbl2;
+-------+--------------+------+-----+---------+-------+
| FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
+-------+--------------+------+-----+---------+-------+
| COL1  | NUMBER       | YES  | UNI | NULL    | NULL  |
| COL2  | VARCHAR2(50) | YES  | NULL| NULL    | NULL  |
+-------+--------------+------+-----+---------+-------+
2 rows in set

In this example, the col1 column must not contain duplicate values.

Example 5: Create a FOREIGN KEY constraint on the tbl3 table.

obclient [SYS]> CREATE TABLE test(c1 NUMBER, c2 NUMBER, CONSTRAINT PK_c1 PRIMARY KEY(c1));
Query OK, 0 rows affected

obclient [SYS]> CREATE TABLE tbl3(col1 NUMBER PRIMARY KEY,col2 NUMBER,CONSTRAINT FK_col2 FOREIGN KEY(col2) REFERENCES test(c1));
Query OK, 0 rows affected

obclient [SYS]> SELECT * FROM ALL_CONSTRAINTS\G
*************************** 1. row ***************************
            OWNER: SYS
  CONSTRAINT_NAME: FK_COL2
  CONSTRAINT_TYPE: R
       TABLE_NAME: TBL3
 SEARCH_CONDITION: NULL
          R_OWNER: SYS
R_CONSTRAINT_NAME: PK_C1
      DELETE_RULE: NO ACTION
           STATUS: ENABLED
       DEFERRABLE: NOT DEFERRABLE
         DEFERRED: IMMEDIATE
        VALIDATED: VALIDATED
        GENERATED: NULL
              BAD: NULL
             RELY: NULL
      LAST_CHANGE: NULL
      INDEX_OWNER: NULL
       INDEX_NAME: NULL
          INVALID: NULL
     VIEW_RELATED: NULL
*************************** 2. row ***************************
            OWNER: SYS
  CONSTRAINT_NAME: PK_C1
  CONSTRAINT_TYPE: P
       TABLE_NAME: TEST
 SEARCH_CONDITION: NULL
          R_OWNER: NULL
R_CONSTRAINT_NAME: NULL
      DELETE_RULE: NULL
           STATUS: ENABLED
       DEFERRABLE: NOT DEFERRABLE
         DEFERRED: IMMEDIATE
        VALIDATED: VALIDATED
        GENERATED: NULL
              BAD: NULL
             RELY: NULL
      LAST_CHANGE: NULL
      INDEX_OWNER: SYS
       INDEX_NAME: PK_C1
          INVALID: NULL
     VIEW_RELATED: NULL
*************************** 3. row ***************************
            OWNER: SYS
  CONSTRAINT_NAME: TBL3_OBPK_1666339106039791
  CONSTRAINT_TYPE: P
       TABLE_NAME: TBL3
 SEARCH_CONDITION: NULL
          R_OWNER: NULL
R_CONSTRAINT_NAME: NULL
      DELETE_RULE: NULL
           STATUS: ENABLED
       DEFERRABLE: NOT DEFERRABLE
         DEFERRED: IMMEDIATE
        VALIDATED: VALIDATED
        GENERATED: NULL
              BAD: NULL
             RELY: NULL
      LAST_CHANGE: NULL
      INDEX_OWNER: SYS
       INDEX_NAME: TBL3_OBPK_1666339106039791
          INVALID: NULL
     VIEW_RELATED: NULL
3 rows in set

In this example, the col2 column in the tbl3 table is associated with the c1 primary key column in the test table. You can view the constraint in the ALL_CONSTRAINTS, DBA_CONSTRAINTS or USER_CONSTRAINTS view.

Example 6: Create a table named tbl4 and add a CHECK constraint on the col1 column to specify that the values of this column must be greater than 10.

obclient [SYS]> CREATE TABLE tbl4(col1 NUMBER CHECK(col1>10),col2 VARCHAR2(50));
Query OK, 0 rows affected

obclient [SYS]> INSERT INTO tbl4 VALUES(2,'CN');
ORA-02290: check constraint violated

obclient [SYS]> INSERT INTO tbl4 VALUES(11,'CN');
Query OK, 1 row affected

In this example, after the CHECK constraint is added on the col1 column, an error is returned if any value inserted into the col1 column is not greater than 10.

Select a partitioning strategy

When you create a table, you must clarify its partitioning strategy. If the table contains a large amount of data, we recommend that you partition the table. You must select a proper partitioning method based on the data to be stored in the table.

In the Oracle compatible mode of OceanBase Cloud, the following partitioning methods are supported: RANGE, LIST, HASH, and composite partitioning. Based on the partitioning dimension, partitioned tables are classified into partitioned tables and subpartitioned tables. Subpartitioned tables are secondary partitioned tables. Therefore, a partitioned table has one partitioning key and a subpartitioned table has two partitioning keys. A subpartitioned table can apply different partitioning strategies.

RANGE partitioning

RANGE partitioning is the most commonly used partitioning method. It is often used when you want to query tables with dates. In RANGE partitioning, the database maps rows to partitions based on ranges of partitioning key values.

Observe the following requirements when you create a RANGE-partitioned table:

• A VALUES LESS THAN(value) clause must be specified for each partition. This clause specifies a non-inclusive upper bound for the partition. Values of the partitioning key equal to or higher than this upper bound are added to the next higher partition.

• All partitions, except the first one, have an implicit lower bound, which is the upper bound of the previous partition.

• You can define a MAXVALUE literal for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than all other values for the partitioning key. A MAXVALUE literal can be defined only for the last partition. MAXVALUE represents a virtual infinite value that is always greater than other possible values for the partitioning key, including the NULL value. If MAXVALUE is specified for the last RANGE partition, you cannot add a new partition.

Example 7: Create a RANGE-partitioned table.

obclient [SYS]> CREATE TABLE tb1_r(col1 NUMBER,col2 NUMBER) 
       PARTITION BY RANGE(col1)
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        ); 
Query OK, 0 rows affected

In this example, the col1 column is used as the partitioning key. The partitioned table tb1_r is partitioned based on the value ranges defined by 100, 200, and 300. p0, p1, and p2 are the partition names, which can be customized. Partition names must be unique in a table.

LIST partitioning

In LIST partitioning, the database uses a list of discrete values as the partitioning key for each partition. The partitioning key consists of one or more columns.

Observe the following requirements when you create a LIST-partitioned table:

• Each partition has a VALUES (value_list) clause.

• The partitioning expression can reference only one column, instead of a list of multiple columns (column vectors).

• If DEFAULT is specified for the last LIST partition, you cannot add more partitions.

Example 8: Create a LIST-partitioned table.

obclient [SYS]> CREATE TABLE tbl2_l (col1 NUMBER,col2 DATE)
         PARTITION BY LIST(col1)
          (PARTITION p0 VALUES (100),
           PARTITION p1 VALUES (200)
          );
Query OK, 0 rows affected

In this example, the col1 column is used as the partitioning key. The tbl2_l table is partitioned based on the value ranges defined by 100 and 200.

HASH partitioning

In HASH partitioning, the database maps rows to partitions based on a hashing algorithm that the database applies to the user-specified partitioning key. ​If you want to evenly distribute data across nodes, we recommend that you use the HASH partitioning method. HASH partitioning is an easy-to-use alternative to RANGE partitioning and is suitable when you want to partition a table that does not contain historical data or a table for which you cannot specify a partitioning key.

Example 9: Create a HASH-partitioned table.

obclient [SYS]> CREATE TABLE tbl3_h(col1 NUMBER,col2 NUMBER)
     PARTITION BY HASH(col1) PARTITIONS 2;
Query OK, 0 rows affected

In this example, the col1 column is used as the partitioning key to partition the tbl3_h table into two partitions. HASH partition names are not specified when the table is created. Therefore, the partitions are named by the system based on the naming rules. To be specific, the partitions are named p0, p1, ..., and pn.

Composite partitioning (subpartitioning)

Composite partitioning partitions a table using one partitioning strategy and partitions each partition using a different partitioning strategy. It is suitable for business tables containing large amounts of data. Composite partitioning gives full play to the advantages of the two partitioning strategies that you use in combination.

Any of RANGE, LIST, and HASH can be used as the subpartitioning strategy of a composite partitioned table. In OceanBase Cloud, subpartitioned tables are classified into template-based subpartitioned tables and non-template-based subpartitioned tables.

The following examples briefly show you how to create a subpartitioned table.

Example 10: Create a RANGE-LIST-subpartitioned table by using a template.

obclient [SYS]> CREATE TABLE tbl4_m_rl(col1 NUMBER,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES('01'),
          SUBPARTITION mp1 VALUES('02'),
          SUBPARTITION mp2 VALUES('03')
          )
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200)
          );
Query OK, 0 rows affected

In this example, the SUBPARTITION TEMPLATE keyword specifies to create a subpartitioned table based on a template. When you use a template to create a subpartitioned table, the template defines subpartitions under each partition. In this case, subpartitions under each partition share the same definition. In this example, the table is partitioned by using the RANGE partitioning method and then subpartitioned by using the LIST partitioning method.

When you create a subpartitioned table by using a template, you do not need to separately specify the name of each subpartition after the subpartition definition is completed. The system names the subpartitions in the ($part_name)s($subpart_name) format based on the naming rules.

Example 11: Create a LIST-HASH-subpartitioned table by using a template.

obclient [SYS]> CREATE TABLE tbl5_m_lh(col1 INT,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
      (PARTITION p0 VALUES('01'),
       PARTITION p1 VALUES('02')
       );
Query OK, 0 rows affected

obclient [SYS]> SHOW CREATE TABLE tbl5_m_lh;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE     | CREATE TABLE


                                                          |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TBL5_M_LH | CREATE TABLE "TBL5_M_LH" (
  "COL1" NUMBER(*,0),
  "COL2" VARCHAR2(50)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list(col1) subpartition by hash(col2) subpartition template (
subpartition P0,
subpartition P1,
subpartition P2,
subpartition P3,
subpartition P4)
(partition P0 values  (1),
partition P1 values  (2)) |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

obclient [SYS]> SELECT table_name,partition_name,subpartition_name FROM USER_TAB_SUBPARTITIONS WHERE table_name = 'TBL5_M_LH';
+------------+----------------+-------------------+
| TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME |
+------------+----------------+-------------------+
| TBL5_M_LH  | P0             | P0SP0             |
| TBL5_M_LH  | P0             | P0SP1             |
| TBL5_M_LH  | P0             | P0SP2             |
| TBL5_M_LH  | P0             | P0SP3             |
| TBL5_M_LH  | P0             | P0SP4             |
| TBL5_M_LH  | P1             | P1SP0             |
| TBL5_M_LH  | P1             | P1SP1             |
| TBL5_M_LH  | P1             | P1SP2             |
| TBL5_M_LH  | P1             | P1SP3             |
| TBL5_M_LH  | P1             | P1SP4             |
+------------+----------------+-------------------+
10 rows in set

In this example, subpartition names are not specified in HASH subpartitioning. The system automatically generates subpartition names P0, P1, P2, P3, and P4 based on the naming rules. Based on the naming rules for template-based subpartitions, subpartitions in the P0 partition are named P0SP0, P0SP1, P0SP2, P0SP3, and P0SP4.

Example 12: Create a LIST-HASH-subpartitioned table without using a template.

obclient [SYS]> CREATE TABLE tbl6_f_lh(col1 NUMBER,col2 VARCHAR2(50))
       PARTITION BY LIST(col1)
       SUBPARTITION BY HASH(col2)
       (PARTITION p0 VALUES('01')
         (SUBPARTITION sp0,
          SUBPARTITION sp1
          ), 
        PARTITION p1 VALUES('02')
         (SUBPARTITION sp2,
          SUBPARTITION sp3,
          SUBPARTITION sp4
          )
        );
Query OK, 0 rows affected

In this example, a subpartitioned table is created without using the SUBPARTITION TEMPLATE keyword or a template. For a non-template-based subpartitioned table, you can define the subpartitions in each partition as needed, without following the same rules. The table is partitioned by using LIST partitioning and then subpartitioned by using HASH partitioning.

Create a replicated table

A replicated table is a special type of table in OceanBase Cloud. Such a table can read the latest modification of data from any "healthy" replica. Replicated tables are suitable for scenarios with low write frequency and high read frequency.

The SQL syntax for creating a replicated table is as follows:

CREATE TABLE table_name column_definition DUPLICATE_SCOPE='none | cluster';

where

• table_name specifies the name of the table.

• column_definition specifies the column information of the table, including column definitions and primary key definitions.

• DUPLICATE_SCOPE specifies the attribute of the replicated table. Valid values are as follows:

  • none: The table is a normal table.
  • cluster: The table is a replicated table. The leader must copy transactions to all full-featured and read-only replicas in the current tenant.

Example 13: Create a replicated table named test_tbl13.

CREATE TABLE test_tbl13 (col1 NUMBER,col2 NUMBER) DUPLICATE_SCOPE= 'cluster';

What to do next

After you create a table, you can perform the following operations:

• Use the INSERT statement to insert data into the table.

• Create indexes on columns in the table to improve the query performance.