Create a table

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

About tables

A database table is a collection of two-dimensional arrays and represents and stores the relationship between database objects. Tables 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.

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 Connect to OceanBase Database.

  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

Use the CREATE TABLE statement to create a table and complete the table creation based on the content of this topic.

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

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

• Confirm whether primary key columns need to be defined for the table based on the requirements for [primary key columns](# Define primary key columns).

• Confirm whether other constraints need to be added to columns based on the requirements for [other constraints](# Define other column constraints).

• We recommend that you specify a default value for a column with the NOT NULL constraint. For a date or time column, you can set the current date or time of the database as the default value.

Define primary key columns

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 the values in the group of one or more columns subject to the constraint uniquely identify the row. You can define only one PRIMARY KEY constraint on a table. The values of the primary key column or columns can be used as the unique identifiers of their corresponding rows. Therefore, each row can be named by a primary key value.

After you create a table with a primary key, the system automatically creates a globally unique index for the primary key column. You can use the primary key to quickly locate rows.

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

Observe the following requirements when you define primary key columns:

• We recommend that you define one primary key column for a table. Each database table can contain a single collection of primary keys.

  In OceanBase Database, users are not forced to define primary key columns for tables. However, primary key columns can help uniquely determine each row in a table and ensure that no duplicate rows exist. If no existing field 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 Sequences.

  OceanBase Database allows you to add a primary key column for an existing table whose primary keys were not specified when the table was created.

• The values of the collection of primary key columns are unique in the table.

• A table can contain a maximum of 64 primary key columns, and the total length of data in the primary key columns cannot exceed 16 KB.

• The values of a primary key column cannot be null or empty. You must enter values to a primary key column.

• We recommend that a PRIMARY KEY constraint be explicitly identified by its name when you define primary key constraints on multiple columns. For example, name a 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, c1 and c2 are defined as primary key columns and a PRIMARY KEY constraint named PK_c1_c2 is defined for them. The values of the c1 and c2 columns cannot be NULL or duplicate.

After you define primary key columns, you can delete or modify them. For more information about operations related to PRIMARY KEY constraints, see Define column constraints.

Define other column constraints

Besides PRIMARY KEY constraints, OceanBase Database also supports NOT NULL, UNIQUE, FOREIGN KEY, and CHECK constraints. Constraints can simplify table queries, improve the query performance, and ensure that data is semantically valid.

The following describes constraints of different types:

• NOT NULL constraint: The constrained column must not contain NULL values.

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

• UNIQUE constraint: The constrained column must not contain duplicate values. NULL values are not prohibited by this constraint type.

• FOREIGN KEY constraint: The constrained column must contain only values from the primary key column of another table.

  If you do not specify the constraint name when you create a FOREIGN KEY constraint, the system automatically allocates a constraint name in the table name_OBFK_creation timestamp format, for example, t1_OBFK_1627747200000000.

  By default, OceanBase Database checks foreign keys. To disable or enable foreign key check, modify the foreign_key_checks tenant variable. For more information about the foreign_key_checks variable, see foreign_key_checks.

• CHECK constraints: 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 delete a table, the CHECK constraints applied to the table are also deleted.

  If you do not specify the constraint name when you create a CHECK constraint, the system automatically allocates a constraint name in the table name_OBCHECK_creation timestamp format, for example: t1_OBCHECK_1629350823880271.

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

Observe the following requirements when you define other column constraints:

• We recommend that you add a NOT NULL constraint to fields without NULL values.

• To reference the values in another table, use FOREIGN KEY constraints.

• Composite primary keys cannot be used as foreign keys.

• To avoid duplicate values in columns, use UNIQUE constraints.

• Except NOT NULL constraints, we recommend that other constraints be explicitly identified by their names. For example, name UNIQUE constraints in the "UNI_xxx" format and FOREIGN KEY constraints in the "FK_xxx" format.

Example 3: Create a table named tbl1 and add 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, when you insert data later, the values inserted into the col1 column cannot be NULL.

Example 4: Create a table named tbl2 and add 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. After the constraint is created, you can query it 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 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.

Partitioned tables in OceanBase Database in Oracle mode can be classified into RANGE, LIST, and HASH partitioned tables by the partitioning strategy. 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 frequently 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.

When you create a RANGE-partitioned table, observe the following requirements:

• 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 partition 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 ranks higher than all other values for the partitioning key. Only the last partition can have an upper bound defined by MAXVALUE, which is an indefinite value that is greater than any other partitioning key value and includes 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, col1 is used as the partitioning key. The tb1_r partitioned table is partitioned by the value ranges defined by 100, 200, and 300. p0, p1, and p2 are the partition names. Partition names must be unique in a table.

LIST partitioning

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

When you create a RANGE-partitioned table, observe the following requirements:

• 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 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 system maps rows to partitions based on the HASH algorithm applied 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, col1 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 as 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.

RANGE, LIST, and HASH can be used as the subpartitioning strategies 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 describe how to create a subpartitioned table. For more examples, see Manage partitions.

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 RANGE partitioning and then subpartitioned by using LIST partitioning.

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. See the following example:

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;
+---------------+----------------+-------------------+
| TABLE_NAME    | PARTITION_NAME | SUBPARTITION_NAME |
+---------------+----------------+-------------------+
| T_RANGE_RANGE | P0             | P0SMP1            |
| T_RANGE_RANGE | P0             | P0SMP2            |
| T_RANGE_RANGE | P0             | P0SMP3            |
| T_RANGE_RANGE | P1             | P1SMP1            |
| T_RANGE_RANGE | P1             | P1SMP2            |
| T_RANGE_RANGE | P1             | P1SMP3            |
| T_RANGE_RANGE | P2             | P2SMP1            |
| T_RANGE_RANGE | P2             | P2SMP2            |
| T_RANGE_RANGE | P2             | P2SMP3            |
+---------------+----------------+-------------------+
9 rows in set

In this example, HASH partitioning is used to subpartition the table without specifying the subpartition names. The system automatically generates the partitions p0, p1, p2, p3, and p4 based on the partition naming rules. The subpartitions of partition p0 are p0smp1, p0smp2, and p0smp3 according to the subpartition naming rules for template-based partitioning.

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 subpartitioning method for each partition. You can choose whether to use the same subpartitioning method for different partitions. In this example, the table is partitioned by using LIST partitioning and then subpartitioned by using HASH partitioning.

What to do next

• Create an index

• Manage partitions