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

Create a table

Last Updated：2025-01-02 01:58:39 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 connected 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 in.
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 privilege, contact the administrator to obtain the privilege. For more information, see Grant direct privileges.

Create a table

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 Oracle mode of OceanBase Database, 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 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 Oracle mode of OceanBase Database, see Overview.
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 section.
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 column(s) that make up this constraint 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 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 a sequence of the Oracle tenant to generate sequential values for this column. For more information about sequences, see Manage sequences.

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".

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 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 constraint: 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 constraint: The constrained column cannot contain duplicate values, but can contain more than one NULL value.
FOREIGN KEY constraint: The constrained column can contain only values from the 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 constraint: enforces that the values of a column in the table must conform to 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 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, the col2 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.

For more information about how to define other column constraints, 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 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.

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 specify MAXVALUE for the highest partition. MAXVALUE represents a virtual infinite value that ranks higher than all other values for the partitioning key. It can be defined only for the last partition and 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 hash 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 Database, 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.

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 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 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 about replicated tables, see Create a replicated table in Create a table.

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:
- 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';

Create a table

Overview

Prerequisites

Note

Create a table

Define the table name

Define columns

Define a primary key column

Define other column constraints

Select a partitioning strategy

Notice

Create a replicated table

Notice

References