Log on to OceanBase Developer Center (ODC) and click the name of the target connection to go to the corresponding connection management page. You can click Table in the left-side navigation pane to get a list of tables. To create a table, click + in the upper-right corner of the table list or click Create in the navigation bar on the top of the page.
You can create a table in six steps, as shown in the following figure.
Set the basic information .
Set columns .
Set partitioning rules. (Optional)
Set the index. (Optional)
Set constraints. (Optional)
Click Commit and wait for the table to be generated
Set the basic information
Specify Table Name and Description for the table. Description is optional. In MySQL mode, you also need to specify Default Character Set and Default Sorting Rule .
Set columns
This is Step 2. You need to specify the following parameters when you add a column, which is also referred to as a field, as shown in the following figure.
| Parameter | Description |
|---|---|
| Field Name | Specifies the name of the column. |
| Data Type | Specifies the data type of the column. |
| Primary Key | Specifies whether the column contains the primary key. |
| Non-empty | Specifies whether the value of the column must be specified. |
| Default Value | Specifies the default value of the column. |
| Comment | Provides additional information about the column. |
The column setting section also provides three buttons:
Add : Click this button to add a field.
Edit : Click this button to edit the selected field. You can also directly double-click a cell to edit it.
Delete : Click this button to delete the selected field.
Set partitioning rules
This is Step 3. In MySQL mode, OceanBase Database supports the following six partitioning methods: Key , Hash , Range , range_columns , List , and List_columns . In Oracle mode, OceanBase Database supports the following three partitioning methods: List , Range , and Hash .
The definition of partition in MySQL mode is different from that in Oracle mode. So, the meanings of the following parameters vary in different modes. You need to specify the following parameters based on the partitioning method that you selected.
| Parameter | Description |
|---|---|
| Partitioning Method | Specifies the partitioning method. The partitioning methods supported in MySQL mode are different from those in Oracle mode. |
| Field | Specifies the field that contains the partition key. |
| Expression | Partitions are divided based on the return value of the expression. The partition expression is not supported in Oracle mode. |
| 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 . You can add multiple partitions and drag the partitions to adjust their orders. |
Set the index
This is Step 4. You need to specify the following parameters, as shown in the following figure:
| Parameter | Description |
|---|---|
| Index Name | Specifies the name of the index. |
| Index Range | The default value is GLOBAL , indicating 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 | Specifies the columns where indexes are created. 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 also provides three buttons:
Add : Click this button to add an index.
Edit : Click this button to edit the selected index. You can also directly double-click a cell to edit it.
Delete : Click this button to delete the selected index.
Set constraints
This is Step 5. The following four table-level constraints are supported in ODC:
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 edit the constraint after it is configured.
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 : connects the data of two tables. A foreign key constraint can be one or more columns and 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 edit existing constraints.
Check Constraints : checks the data in the database based on the configured check rules when you edit the data. Data modification is allowed only after the check is passed. Check Constraints is supported only in Oracle mode.
OceanBase Database supports different constraints in MySQL and Oracle modes, and different constraints require different information. Therefore, you need to specify the required information based on the constraint you selected and the requirements on the page.
| Parameter | Description |
|---|---|
| Constraint Name | Specifies the name of the constraint. |
| Column Information | Specifies one field or a group of fields as the constraint. |
| Enabled | Specifies whether to enable the created constraint. In some cases, you may need to temporarily disable the constraints. For example, when you import a large amount of data, you can disable constraints to improve the import efficiency. |
| Deferrable | Supports three delay states: Verify Now , Non-deferrable , and Delayed Verification . |
| Associated Schema (in Oracle mode) | Specifies the schema where the associated table is located when Foreign Key Constraint is used. The associated table is the parent table. |
| Associated Database (in MySQL mode) | Specifies the database where the associated table is located when Foreign Key Constraint is used. The associated table is the parent table. |
| Associated Table | Specifies the associated table when Foreign Key Constraint is used. The associated table is the parent table. |
| Associated field | Specifies the associated table when Foreign Key Constraint is used. The associated table is 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 are supported: CASCADE , NOACTION , RESTRICT , and SETNULL . |
| 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 are supported: CASCADE , NOACTION , RESTRICT , and SETNULL . |
| Check Condition | Specifies the check rules for data verification when Check Constraint is enabled. |
The constraint setting section also provides three buttons:
Add : Click this button to add a constraint.
Edit : Click this button to edit the selected constraint. You can also directly double-click a cell to edit it.
Delete : Click this button to delete the selected constraint.