Create a table

This topic describes how to create a table by using SQL statements. It also describes the prerequisites, table overview, and requirements for creating a table, and provides some examples.

Table overview

A table is a collection of two-dimensional arrays that represent and store the relationships between data objects. By properly designing and using database tables, you can improve the reliability, consistency, and query performance of data, thereby effectively managing and utilizing the data in the database.

For more information about tables in seekdb, see Table overview.

Prerequisites

Before you create a table, make sure that the following conditions are met:

• You have deployed seekdb. For more information about how to deploy seekdb, see Overview.

• You have connected to seekdb.

• You have created a database. For more information about how to create a database, see Create a database.

• You have the CREATE privilege. For information about how to view the privileges of the current user, see View user privileges. If you do not have the privilege, contact the administrator to grant the privilege. For information about how to directly grant privileges, see Directly grant privileges.

Create a table by using a CLI

You can run the CREATE TABLE statement to create a table.

info

You can run the SHOW TABLES; statement to query the information about tables in the database.

Define a table name

Before you create a table, you must specify a table name. The following requirements must be met when you define a table name:

• In seekdb, the name of each table must be unique within the database.

• The length of a table name cannot exceed 64 characters.

• We recommend that you use meaningful names for tables. We do not recommend that you use names such as t1 or table1. For more information about table naming, see Table naming.

Example 1: Create a table to store order information.

tip

The following SQL statement cannot be executed because no columns are defined.

CREATE TABLE orders (...);

Define columns

In a database, a column (Column) is used to record the value of a field of an attribute of a table. The name of an attribute is the name of a column. In addition to the column name, a column has a data type and the maximum length (precision) of the data type.

The following requirements must be met when you define columns:

• Select an appropriate data type for the data to be stored in the column based on the characteristics of the data type.

  For more information about the data types supported in seekdb, see 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 specified maximum length is greater than the maximum number of characters to be stored to avoid character truncation.

• Determine whether to define a primary key column for the table based on the requirements of Primary key columns.

• Determine whether to add other constraints to the column based on the requirements of Other constraints.

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

Define primary key columns

A primary key value rule is a rule defined on a key (a key refers to a column or a set of columns). The rule ensures that each data row in a table can be uniquely identified by a key value. A database table can have only one PRIMARY KEY constraint. The values of the columns that constitute the constraint can be used as the unique identifier of a data row. Therefore, each data row can be uniquely identified by the primary key value.

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

The following requirements must be met when you define primary key columns:

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

  In seekdb, you are not required to define a primary key for a table. However, using a primary key ensures that each data row in a table is uniquely identified and that no duplicate data rows exist. If no suitable field is available for the primary key, you can omit the primary key when you create a table. After the table is created, the system automatically specifies an auto-increment column as a hidden primary key. For more information about auto-increment columns, see Define an auto-increment column.

  In addition, if you do not define a primary key column when you create a table, you can add a primary key column to an existing table in seekdb.

• The values of the primary key column set must be unique in the entire table.

• The number of primary key columns cannot exceed 64, and the total length of the primary key data cannot exceed 16 KB.

• The values of the primary key columns cannot be NULL or empty strings. You must enter values for the primary key columns.

• We recommend that you explicitly specify the name of the primary key constraint. For example, you can name the primary key constraint "PK_xxx".

For more information about primary key constraints, see Primary key constraints.

Example 2: Define a primary key constraint on multiple columns.

CREATE TABLE test(c1 INT, c2 INT, CONSTRAINT PK_c1_c2 PRIMARY KEY(c1, c2));
Query OK, 0 rows affected

desc test;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | NO   | PRI | NULL    |       |
| c2    | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

In Example 2, the c1 and c2 columns are defined as primary key columns, and the constraint name is PK_c1_c2. The values of the c1 and c2 columns cannot be NULL and must be unique.

After you define primary key columns, you can delete the primary key. For more information about primary key constraints, see Define the constraint type of a column.

Define other column constraints

In addition to the PRIMARY KEY constraint, seekdb supports the NOT NULL constraint, the unique (UNIQUE) constraint, the foreign key (FOREIGN KEY) constraint, and the CHECK constraint. Using constraints can simplify table queries, improve query performance, and ensure that data remains semantically valid.

The following table describes the constraint types:

• NOT NULL constraint: specifies that the values of the constrained column cannot be NULL.

  For a column with the NOT NULL constraint, you must specify a value in the INSERT statement, unless the column also has a non-null default value.

• UNIQUE constraint: specifies that the values of the constrained column cannot be duplicated, but multiple NULL values are allowed.

• FOREIGN KEY constraint: specifies that the values of the constrained column must be values of the primary key column of another table.

  When you create a FOREIGN KEY constraint without specifying a constraint name, the system automatically assigns a constraint name. The automatically assigned constraint name is table_name_OBFK_creation_timestamp. For example, t1_OBFK_1627747200000000.

  By default, seekdb enables the FOREIGN KEY constraint check. The FOREIGN KEY constraint check is controlled by the variable foreign_key_checks. For more information about the foreign_key_checks variable, see foreign_key_checks.

• CHECK constraint: specifies that the values of the constrained column must meet the specified condition.

  You can define one or more CHECK constraints for a single column to allow only specific values for the column. You can also define a table-level CHECK constraint to apply a single CHECK constraint to multiple columns. When you rename a table, the name of the CHECK constraint remains unchanged. When you drop a table, all CHECK constraints applied to the table are also dropped.

  When you create a CHECK constraint without specifying a constraint name, the system automatically assigns a constraint name. The automatically assigned constraint name is table_name_OBCHECK_creation_timestamp. For example, t1_OBCHECK_1629350823880271.

To define a constraint for a single column, add a constraint keyword in the column definition. To define a constraint for multiple columns, add the entire constraint definition after the list of all columns in the CREATE TABLE statement.

The following requirements apply when you define other column constraints:

• We recommend that you add the NOT NULL constraint to a field that does not contain NULL values.

• If you want to reference values in another table, use the FOREIGN KEY constraint.

• A composite primary key cannot be used as a foreign key.

• To prevent duplicate values in a column, use the UNIQUE constraint.

• We recommend that you explicitly specify the names of other constraints. For example, name a UNIQUE constraint "UNI_xxx" and a FOREIGN KEY constraint "FK_xxx".

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

CREATE TABLE tbl1(col1 INT NOT NULL,col2 INT);
Query OK, 0 rows affected

DESC tbl1;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   |     | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

In Example 3, the value of the col1 column cannot be NULL when you insert data.

Example 4: Create a table named tbl2 and set the col1 column to have the UNIQUE constraint.

CREATE TABLE tbl2(col1 INT UNIQUE,col2 INT);
Query OK, 0 rows affected

desc tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | YES  | UNI | NULL    |       |
| col2  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

In Example 4, the col1 column cannot have duplicate values.

Example 5: Create a FOREIGN KEY constraint.

CREATE TABLE test(c1 INT, c2 INT, CONSTRAINT PK_c1 PRIMARY KEY(c1));
Query OK, 0 rows affected

CREATE TABLE tbl3(col1 INT PRIMARY KEY,col2 INT,CONSTRAINT FK_col2 FOREIGN KEY(col2) REFERENCES test(c1));
Query OK, 0 rows affected

SELECT * FROM information_schema.TABLE_CONSTRAINTS;
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME               | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
| def                | xxx               | PRIMARY                       | xxx          | test       | PRIMARY KEY     | YES      |
| def                | xxx               | PRIMARY                       | xxx          | tbl3       | PRIMARY KEY     | YES      |
| def                | xxx               | FK_col2                       | xxx          | tbl3       | FOREIGN KEY     | YES      |
+--------------------+-------------------+-------------------------------+--------------+------------+-----------------+----------+
3 rows in set

In Example 5, the col2 column of the tbl3 table is associated with the primary key column c1 of the test table. After the constraint is created, you can query the information_schema.TABLE_CONSTRAINTS view to view the constraint.

Example 6: Create a table named tbl4 and set the col1 column to have the CHECK constraint.

CREATE TABLE tbl4(col1 INT CHECK(col1>10),col2 INT);
Query OK, 0 rows affected

INSERT INTO tbl4 VALUES(2,2);
ERROR 3819 (HY000): check constraint violated

INSERT INTO tbl4 VALUES(11,2);
Query OK, 1 row affected

In Example 6, the col1 column has the CHECK constraint. Therefore, if you insert a value that is not greater than 10 into the col1 column, an error is returned.

For more information about how to define other column constraints, see Define column constraints.

Define an auto-increment column

In seekdb, if you want a numeric column in a table to have unique and incrementing values, you can define the column type as AUTO_INCREMENT, which is an auto-increment column.

An auto-increment column has three important attributes: the auto-increment start value, the auto-increment step, and the auto-increment cache size. These attributes are controlled by the three variables auto_increment_cache_size, auto_increment_increment, and auto_increment_offset.

Variable Name	Description
auto_increment_cache_size	A global variable that specifies the number of values cached for auto-increment. The value ranges from [1, 100000000], and the default value is 1000000.
auto_increment_increment	A session variable that specifies the auto-increment step. The value ranges from [1, 65535], and the default value is 1.
auto_increment_offset	A session variable that specifies the starting value of the AUTO_INCREMENT column. The value ranges from [1, 65535], and the default value is 1.

You can modify the values of these system variables based on your business requirements. For more information about how to modify system variables, see Configuration Management.

The following requirements apply when you define an auto-increment column:

• The AUTO_INCREMENT attribute applies only to integer data columns.

• The data column with the AUTO_INCREMENT attribute must have the NOT NULL attribute.

• When you create a partitioned table and use an auto-increment column as the partitioning key, the values of the auto-increment column are globally unique, but not necessarily incrementing within each partition.

After an auto-increment column is created, if you specify a value for the column in an INSERT statement and the system variable SQL_MODE is not set to NO_AUTO_VALUE_ON_ZERO, the system uses the next value of the auto-increment column to fill the column if the specified value is 0. If the specified value is less than the current maximum value, it does not affect the calculation of the next value of the auto-increment column. If the specified value is greater than the current maximum value, the auto-increment column uses the sum of the specified value and the cached value as the starting value for the next auto-increment.

info

If the system variable SQL_MODE is set to NO_AUTO_VALUE_ON_ZERO, the system does not generate an AUTO_INCREMENT value for a column with a value of 0.

Example 7: Create a table with an auto-increment column.

CREATE TABLE personal_info(id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(50), gmt_create timestamp NOT NULL default current_timestamp);
Query OK, 0 rows affected

In Example 7, the id column is an auto-increment column. Therefore, when you use the INSERT statement to insert data, you do not need to specify a value for the auto-increment column. The system automatically fills the column with values, as shown in the following example.

INSERT INTO personal_info(name) VALUES('A'),('B'),('C');
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

SELECT * FROM personal_info;
+----+------+---------------------+
| id | name | gmt_create          |
+----+------+---------------------+
|  1 | A    | 2025-12-19 17:33:06 |
|  2 | B    | 2025-12-19 17:33:06 |
|  3 | C    | 2025-12-19 17:33:06 |
+----+------+---------------------+
3 rows in set

For more information about auto-increment columns, see Define an auto-increment column.

Choose a partitioning scheme

When you create a table, you must specify the partitioning scheme. If the table contains a large amount of data, we recommend that you partition the table. When you create a partitioned table, you must select an appropriate partitioning scheme based on the data to be stored in the table.

In seekdb, the maximum number of partitions supported for a single table is specified by the max_partition_num parameter. The default value is 8192.

In seekdb, partitioned tables can be categorized into the following types based on the partitioning strategy:

• Range partitioning / Range columns partitioning

• List partitioning / List columns partitioning

• Hash partitioning / Key partitioning

• Composite partitioning

Based on the partitioning dimension, partitioned tables can be categorized into primary partitions and subpartitions. Subpartitions are the further division of primary partitions. Therefore, a primary partition table contains one partitioning key, and a subpartition table contains two partitioning keys. The partitioning strategies for the two divisions can be different. In seekdb, subpartition tables can be categorized into templated subpartition tables and non-templated subpartition tables.

For more information about partitions, see Overview.

Range partitioning / Range columns partitioning

Range partitioning and Range columns partitioning are both based on the range of partitioning keys for each partition. These partitioning schemes are suitable for queries that require partitioning based on a range of partitioning keys. For example, you can range partition a table by using a time field or a price range.

The differences between range partitioning and range columns partitioning are as follows:

• In range partitioning, the partitioning key must be an integer. If you want to range partition a table by using a date field, you must convert the date field into an integer by using a function. For example, you can use the YEAR() function to convert the date field into an integer. In range columns partitioning, the partitioning key can be any type.

• In range partitioning, you can write an expression as the partitioning key, but you cannot write a column (column vector) as the partitioning key. For example, you cannot write partition by range(c1, c2). In range columns partitioning, you cannot write an expression as the partitioning key, but you can write multiple columns (column vectors) as the partitioning key.

You can use the VALUES LESS THAN(value) keyword to define each partition in range partitioning and range columns partitioning. In this keyword, the value parameter can only be a continuous and non-overlapping value that is in ascending order.

Example 8: Create a range columns partitioned table.

CREATE TABLE tb1_rc(col1 INT,col2 INT)
      PARTITION BY RANGE COLUMNS(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 Example 8, the partitioning key of a range columns partitioned table can be any type. Therefore, you can use the col1 column as the partitioning key. The tb1_rc table is partitioned by using the 100, 200, and 300 values as the partitioning keys. The p0, p1, and p2 values are the names of the partitions. You can customize the partition names as long as the names of the partitions in the same table do not overlap.

List partitioning / List columns partitioning

List partitioning is based on specific values. The values of each partition do not overlap. List partitioning is suitable for partitioning unordered or unrelated data sets.

If you want to use list partitioning by using multiple columns or other data types, you can use list columns partitioning. List columns partitioning is an extension of list partitioning. It supports multiple partitioning keys and supports INT, DATE, and DATETIME data types.

The differences between list partitioning and list columns partitioning are as follows:

• In list partitioning, the partitioning key must be an integer. In list columns partitioning, the partitioning key can be any type.

• In list partitioning, only one partitioning key is supported. The partitioning key can be a column or an expression. In list columns partitioning, the partitioning key cannot be an expression, but can be multiple columns (column vectors).

You can use the VALUES IN(value_list) keyword to define each partition in list partitioning and list columns partitioning.

Example 9: Create a list partitioned table.

CREATE TABLE tbl2_l (col1 INT,col2 DATE)
      PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (100),
       PARTITION p1 VALUES IN (200)
      );
  Query OK, 0 rows affected

In Example 9, the partitioning key of a list partitioned table must be an integer. Therefore, you can use the col1 column as the partitioning key. The tbl2_l table is partitioned by using the 100 and 200 values as the partitioning keys.

Hash partitioning / Key partitioning

Hash partitioning requires that you specify the partitioning key and the number of partitions. The system calculates an integer by using the hash expression of the partitioning key. Then, the system determines the partition to which a row belongs by taking the modulus of the result with the number of partitions.

Key partitioning is similar to hash partitioning. In both partitioning schemes, the system determines the partition to which a row belongs by taking the modulus of the number of partitions. The difference is that the system performs a hash function on the key partitioning key before taking the modulus. Therefore, you cannot determine the partition to which a row belongs by using simple calculations.

The differences between key partitioning and hash partitioning are as follows:

• In hash partitioning, the partitioning key must be an integer. In key partitioning, the partitioning key can be any type, and character columns are supported.

• In hash partitioning, the partitioning key can be an expression. In key partitioning, the partitioning key cannot be an expression.

Example 10: Create a hash partitioned table tbl3_h.

CREATE TABLE tbl3_h(col1 INT,col2 VARCHAR(50))
     PARTITION BY HASH(col1) PARTITIONS 2;
Query OK, 0 rows affected

In Example 10, the partitioning key of a hash partitioned table must be an integer. Therefore, you can use the col1 column as the partitioning key. The tbl3_h table is divided into two partitions. When you create a table without specifying the partition names, the system generates the partition names based on the naming rules. The partition names are p0, p1, and so on.

Composite partitioning (subpartitioning)

Composite partitioning is suitable for business tables that contain a large amount of data. Composite partitioning involves using one partitioning strategy for primary partitions and another partitioning strategy for subpartitions.

Range partitioning, range columns partitioning, list partitioning, list columns partitioning, hash partitioning, and key partitioning can all be used as subpartitioning strategies in a composite partitioned table. In seekdb, subpartitioned tables can be categorized into templated subpartitioned tables and non-templated subpartitioned tables.

The following examples show how to create a subpartitioned table.

Example 11: Create a templated range columns + range partitioned table.

CREATE TABLE tb1_m_rcr(col1 INT,col2 INT)
         PARTITION BY RANGE COLUMNS(col1)
         SUBPARTITION BY RANGE(col2)
         SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(3),
           SUBPARTITION mp1 VALUES LESS THAN(6),
           SUBPARTITION mp2 VALUES LESS THAN(9)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          );
Query OK, 0 rows affected

SELECT table_name,partition_name,subpartition_name FROM information_schema.partitions;
+------------+----------------+-------------------+
| table_name | partition_name | subpartition_name |
+------------+----------------+-------------------+
| tb1_m_rcr  | p0             | p0smp0            |
| tb1_m_rcr  | p0             | p0smp1            |
| tb1_m_rcr  | p0             | p0smp2            |
| tb1_m_rcr  | p1             | p1smp0            |
| tb1_m_rcr  | p1             | p1smp1            |
| tb1_m_rcr  | p1             | p1smp2            |
| tb1_m_rcr  | p2             | p2smp0            |
| tb1_m_rcr  | p2             | p2smp1            |
| tb1_m_rcr  | p2             | p2smp2            |
+------------+----------------+-------------------+
9 rows in set

In Example 11, the partitioning key of a range partitioned table must be an integer. Therefore, you can use the col2 column as the partitioning key. You can use the SUBPARTITION TEMPLATE keyword to create a templated subpartitioned table. In a templated subpartitioned table, the subpartitions of each primary partition are defined based on the subpartition definitions in the template. That is, the subpartition definitions of each primary partition are the same. In this example, range columns partitioning is used to create primary partitions, and range partitioning is used to create subpartitions.

When you create a templated subpartitioned table, you do not need to specify the names of each subpartition. The system generates the subpartition names based on the naming rules. The naming rule for a subpartition is ($part_name)s($subpart_name). In this example, the subpartitions of the p0 primary partition are named p0smp0, p0smp1, and p0smp2.

tip

For hash partitioning or key partitioning, if you specify the number of subpartitions by using the SUBPARTITIONS 5 clause, you do not need to specify the SUBPARTITION TEMPLATE keyword when you create a templated subpartitioned table.

Example 12: Create a non-templated list + list columns partitioned table.

CREATE TABLE tbl2_f_llc(col1 INT,col2 DATE)
       PARTITION BY LIST(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES IN('2021/04/01'),
            SUBPARTITION sp1 VALUES IN('2021/07/01'),
            SUBPARTITION sp2 VALUES IN('2021/10/01'),
            SUBPARTITION sp3 VALUES IN('2022/01/01')
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES IN('2021/04/01'),
            SUBPARTITION sp5 VALUES IN('2021/07/01'),
            SUBPARTITION sp6 VALUES IN('2021/10/01'),
            SUBPARTITION sp7 VALUES IN('2022/01/01')
            )
         );
Query OK, 0 rows affected

In Example 12, you need to define subpartitions for each primary partition. The subpartition definitions of each primary partition can be the same or different.

Example 13: Create a non-templated hash + key partitioned table.

CREATE TABLE tbl3_f_hk (col1 INT,col2 VARCHAR(50))
     PARTITION BY HASH(col1)
     SUBPARTITION BY KEY(col2)
      (PARTITION p1
       (SUBPARTITION sp0
       ,SUBPARTITION sp1
       ,SUBPARTITION sp2
       ,SUBPARTITION sp3
       ),
       PARTITION p2
       (SUBPARTITION sp4
       ,SUBPARTITION sp5
       ,SUBPARTITION sp6
       ,SUBPARTITION sp7
       )
      );
Query OK, 0 rows affected

In Example 13, since the key partition supports character columns as the partitioning key, you can use the col2 column as the partitioning key for subpartitioning. In this example, sp0, ..., sp7 are the specified subpartition names.

What to do next

After you create a table, you may need to perform some of the following operations to manage the table:

• After you create a table, you can use the INSERT statement to insert data into the table. For more information about how to insert data, see Insert data.

• To improve query performance, you can create indexes on the columns of the table. For more information about how to create indexes, see Create an index.