Create a table|V4.2.0| docs|Distributed Database

Create a table

Last Updated：2023-11-24 02:58:23 Updated

This topic describes the requirements and examples for creating a table.

Overview

A database table is a collection of two-dimensional arrays that represent and store the relationships between different objects in a database. Tables in OceanBase Database can be classified into partitioned tables and non-partitioned tables based on the distribution of data.

A partitioned table contains multiple partitions. When you create a partitioned table, you must specify at least the partitioning columns and related partition information.
A non-partitioned table contains only one partition.

For more information about tables, see Overview of tables.

Prerequisites

Before you create a table, make sure that:

You have logged on to an Oracle tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Connection methods.

Note

You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.
You have the CREATE TABLE privilege. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator. For more information, see Modify user privileges.

Create a table

You can use the CREATE TABLE statement to create a table and complete the table creation based on the following content.

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 mode of OceanBase Database, the name of each table must be unique in a 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 a 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 more information about the data types supported in the Oracle mode of OceanBase Database, see Overview of data types.
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 requirement 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 the PRIMARY KEY constraint

A PRIMARY KEY constraint imposes a primary key value rule on a key, which can be a column or a 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 specify a column as the primary key, add the PRIMARY KEY keyword to the definition of the column. If you want to define the PRIMARY KEY constraint on 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 the PRIMARY KEY constraint:

We recommend that you define a primary key for each table. Each database table has at most one primary key.

Although you are not forced to define a primary key for a table in OceanBase Database, 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 the Sequence object of the Oracle tenant to generate sequential values for this column. For more information about sequences, see Manage sequences.

If you did not define a primary key during table creation, you can add a primary key to the table after it is created.
The values in the primary key must be unique in the entire table.
A primary key can contain at most 64 columns, and the total length of primary key data cannot exceed 16 KB.
Values in the primary key cannot be NULL or empty strings. You must enter values for the primary key.
When you define a PRIMARY KEY constraint on multiple columns, we recommend that you name the constraint. For example, you can name the PRIMARY KEY constraint "PK_xxx".

For more information about the PRIMARY KEY constraint, see PRIMARY KEY constraints.

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. For more information, see Define column constraints.

Define other column constraints

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

The constraints 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 additional 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 set a UNIQUE constraint on the col2 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, values in the col2 column cannot be duplicate.

Example 5: Set a FOREIGN KEY constraint on a column of 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 of the tbl3 table is associated with the primary key column c1 of another table test. You can view the constraint in the ALL_CONSTRAINTS, DBA_CONSTRAINTS, or USER_CONSTRAINTS view.

Example 6: Create a table named tbl4 and set a CHECK constraint on the col1 column to ensure that values in the column are 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, because a CHECK constraint is added to the col1 column, an error occurs if a value inserted into the col1 column is not greater than 10.

For more information, see Define column constraints.

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 mode of OceanBase Database, 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.

For more information about partitions, see Overview of partitions.

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 tb1_r table 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 column values of 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 split the tbl3_h table into two partitions. The names of HASH partitions are not specified when the table is created. Therefore, the system names the partitions p0, p1, ..., and pn based on the naming rules.

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.

RANGE partitioning, LIST partitioning, and HASH partitioning can be used as subpartitioning strategies for composite partitioned tables. In OceanBase Database, subpartitioned tables are classified into template-based subpartitioned tables and non-template-based subpartitioned tables.

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

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

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 is used to create a template-based subpartitioned table. 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, LIST subpartitioning is performed after RANGE partitioning.

In addition, when you create a template-based subpartitioned-table, you do not need to name each subpartition after define the subpartitions. The system names the subpartitions in the format of ($part_name)s($subpart_name) based on the naming rules.

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

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.

Notice

If you use HASH partitioning to subpartition a table by specifying the number of subpartitions, such as SUBPARTITIONS 5, you do not need to specify the SUBPARTITION TEMPLATE keyword when you create a subpartitioned table by using a template.

Example 12: Create a non-template-based LIST-HASH-subpartitioned table.

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, the SUBPARTITION TEMPLATE keyword does not need to be added when the non-template-based subpartitioned table is created. For a non-template-based subpartitioned table, you can define the subpartitions in each partition as needed, without following the same rules. In this example, HASH subpartitioning is performed after LIST partitioning.

Create a replicated table

A replicated table is a special type of table in OceanBase Database. 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.

For more information, see Create a replicated table in Create a table in the Administrator Guide.

Notice

Replicated tables can be created only in user tenants but not in the sys tenant.

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 include:
- none: The table is a normal table.
- cluster: The table is a replicated table. The leader must copy transactions to all F and R 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';

Create a table

Overview

Prerequisites

Note

Create a table

Define the table name

Define columns

Define the PRIMARY KEY constraint

Define other column constraints

Select a partitioning strategy

Notice

Create a replicated table

Notice

References