Create a table in ODC|V3.2.4|OceanBase Database| docs|Distributed Database

Create a table in ODC

Last Updated：2023-10-27 09:57:43 Updated

OceanBase Developer Center (ODC) allows you to create a table on a GUI. This topic describes how to create a table in ODC.

Prerequisites

A database connection in MySQL mode is established.

Overview

Create a table

The preceding figure shows how to create a table. You can create a table in the following seven steps:

Specify the basic information.
Set columns.
Set partitioning rules.
Set the index.
Set constraints.
Verify the SQL statement.
Complete the table creation.

Procedure

In the following example, a table named employee is created in the ODC console. The table contains the emp_no, birthdate, name, and gender columns. Perform the following steps:

Step 1: Specify the basic information

Log on to the ODC console and click the name of the desired connection to go to the corresponding connection management page. You can click Table in the left-side navigation pane to view tables. To create a table, click + in the upper-right corner of the table list or click Create in the top navigation bar.

In the Basic Info section, set the Table Name and Description parameters.

Note
In MySQL mode, you also need to set the Default Character Set and Default Sorting Rule parameters.

Step 1

Step 2: Set columns

The following figure and table show the information that you need to specify when you add a column.

Step 2

Parameter	Description
Field Name	The name of the column.
Data Type	The data type of the column. For more information about data types, see Data type overview.
Non-empty	Specifies whether the value of the column is required.
Auto-increment	Specifies whether the column is an auto-increment column. This parameter is valid in MySQL mode.
Default Value	The default value of the field or column.
Comment	The additional information about the column.

The column setting section provides three buttons.

Button	Description
Create	Click this button to create a field.
Edit	Click this button to modify the selected field. You can also directly double-click a cell to modify the field.
Delete	Click this button to delete the selected field.

Step 3: Set partitioning rules

Step 3

You can partition a table that contains a large amount of data. After a table is partitioned, data in the table is stored in multiple tablespaces. The database does not scan the entire table for a query.

In MySQL mode, OceanBase Database supports the following partitioning methods: KEY, HASH, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS. Set the following parameters based on the selected partitioning method.

Parameter	Description
Partitioning Method	Specifies the partitioning method. RANGE partitioning: Multiple rows with column values in an ordered, continuous, and non-overlapping range are assigned to a partition. LIST partitioning: A table is partitioned based on the enumeration values. HASH partitioning: A table is partitioned based on a given number of partitions. For a table that stores data without a clear pattern or range, you can use HASH partitioning to randomly distribute values of the partitioning column to different partitions based on the hash algorithm. KEY partitioning: This partitioning method is similar to HASH partitioning. The difference is that KEY partitioning only supports the computation of one or more columns, and the MySQL server provides its own hash function. At least one INT type column is required for Key partitioning. For more information, see About partitioned tables.
Field	The column that is used as the partition key.
Expression	The table is partitioned based on the return value of the expression.
Partition	Based on the value specified for Partitioning Method, you may need to specify information such as Partition Name, Partition Quantity, Upper limit of the range, and Enumeration Value. Note If you select RANGE partitioning and the table contains a large amount of data, enter MAXVALUE in Upper limit of the range, which indicates that no upper limit is set for the range. You can add multiple partitions and drag selected fields to adjust their order.

Step 4: Set the index

If a table contains a large amount of data, you can use indexes to accelerate data queries. An index is a data structure that pre-sorts the values of one or more columns in a table. By using indexes, you can directly locate records that meet the conditions.

The following figure and table show the information that you need to specify when you set the index.

Step 4

Parameter	Description
Index Name	The name of the index.
Index Range	The default value is GLOBAL, which indicates a global index. You can set this parameter to LOCAL only for tables with partitions. The value LOCAL indicates a local index.
Index Type	Only B-tree is supported.
Optional Field	The columns to be indexed. Pay attention to the order of the indexed columns.
Unique	Specifies whether the index is unique. In other words, the index is used to ensure that the constraint is unique.

The index setting section provides three buttons.

Button	Description
Create	Click this icon to create an index.
Edit	Click this button to modify the selected index. You can also directly double-click a cell to modify the index.
Delete	Click this icon to delete the selected index.

Step 5: Set constraints

Constraints are used to specify data rules for a table. A data operation that violates the constraints is terminated.

Step 5

ODC supports the following four table constraints:

Primary Key Constraint: defines a primary key to uniquely identify each row of data in the table. A primary key constraint can be a field or a group of fields. You can set only one primary key constraint for a table, and you cannot modify the primary key constraint after you configure the constraint.
Unique Constraint: ensures that the data in a field or a group of fields is unique in the table. You can set multiple unique constraints in one table.
Foreign Key Constraint: associates one or more columns in two tables. A foreign key constraint is used to maintain the data consistency and integrity between associated tables. After you complete the setting of foreign key constraints, you cannot create new constraints or modify existing constraints.

Specify the required information based on the constraint that you select and the requirements on the page.

Parameter	Description
Constraint Name	The name of the constraint.
Column Information	The field or a group of fields specified as the constraint.
Associated Database	The database where the associated table is located when Foreign Key Constraint is used. The associated table is the parent table. This field is valid only in MySQL mode.
Associated Table	The associated table when Foreign Key Constraint is used. The associated table is the parent table.
Associated Field	The associated field when Foreign Key Constraint is used. The associated field is in the parent table.
Delete	Specifies the action to be performed on the current table when the data in the associated table is deleted. The current table is the child table, and the associated table is the parent table. The following four types of actions can be specified: CASCADE, NO ACTION, RESTRICT, and SET NULL.
Update	Specifies the action to be performed on the current table when the data in the associated table is updated. The current table is the child table, and the associated table is the parent table. The following four types of actions can be specified: CASCADE, NO ACTION, RESTRICT, and SET NULL.

The constraint setting section provides three buttons.

Button	Description
Create	Click this button to create a constraint.
Edit	Click this button to modify the selected constraint. You can also directly double-click a cell to modify the constraint.
Delete	Click this icon to delete the selected constraint.

Step 6: Confirm the SQL statement

Click Submit to go to the SQL Confirmation page.

You can modify the SQL statement on the SQL Confirmation page. Syntax:

CREATE TABLE table_name (column_name column_type, column_name column_type,.......);

Parameters

Parameter	Description
CREATE TABLE	The key word that indicates the creation of a table with a specified name. You must have the privileges to create tables.
Table Name	The name of the table. The table name must conform to the identifier naming rules.
column_name column_type	The name and data type of the column in the table. To create multiple columns, separate the tuple with commas (,).

Step 7: Complete the table creation

Click Execute. After the table is created, the employee table appears in the table list in the left-side navigation pane.

Note
To manage a table, right-click the table name in the left-side navigation pane, and select the required operation from the context menu, which provides the following options: View, Create, Single Table Import, Single Table Export, Download, Mock Data, Open SQL Window, Copy, Rename, Delete, and Refresh.

For more information, see Manage table data.

You can use the SELECT statement to query data in the new table.

Syntax:

SELECT
  column_name,
  column_name
FROM
  table_name [WHERE Clause] [LIMIT N] [ OFFSET M]