Designing MySQL table structure

NineData provides a visual table structure design solution. User can click the mouse in the SQL console to complete the table structure design without manually entering SQL commands.

Prerequisite

• Database is MySQL.

• In Organizational mode, user has the SQL console function module authority.

• In Organizational mode, user has read and write permissions to the target data source.

  tip

  If user do not have the above permissions, please contact organization's system administrator to Configure permissions or Bind roles.

• In the commercial versions (DevOps Pro, DevOps Enterprise), please ensure that your monthly/yearly subscription is not expired, as it may result in the inability to use the Database DevOpsservice. You can quickly check the remaining quota and expiration date at the top right corner of the NineData console page.

  

Steps

User can design the table structure when creating or editing a table through the SQL console. This article takes creating a table as an example to demonstrate.

1. Open the SQL console and go to the target data source.

2. In the database list on the left side of the SQL console, right-click the target database and click Create Table .

   

   tip

   The data table will be created in the target database.

3. On the Create Table tab, user can start to visually design the table structure, and the system will automatically generate SQL Script at the bottom of the page according to the configuration. For details of the variable structure design page, please select the corresponding tab below and view it.

   

Click to expand the full visual parameter description of the table structure.

Common

• **Name**: The name of the custom table.
• **Database**: Select the database under which the current table needs to be created. The default is the database right-clicked in step 2.
• **Comment**: Comments for the table, that is, the COMMENT attribute.

Column

Set columns.

• **Columns**: In the leftmost text box, enter the name of the column, up to 64 characters in length. The first row in the current interface defaults to the primary key column, and the column name defaults to id.
• **Data Type**: The second text box from the left, click the text box to select the data type of the column.
• NOT NULL: Sets whether the column allows null values. The primary key column of the first row in the current interface, this option is fixedly selected, that is, null values are not allowed.
• **Default**: Set the default value of the current column, that is, the DEFAULT property. If no other value is inserted into the column, the default value is used.
• **Comment**: Set the comment of the current column, that is, the COMMENT property. Set a description for the column with a comment. The maximum length is 1024 characters.
• PRIMARY KEY: Set whether the current column is the primary key column, by selecting the check box to the left of PRIMARY KEY, or clicking the magnifying glass icon to the left of the column name to open, multiple columns can serve as primary key columns to form a composite primary key.
• AUTO_INCREMENT: Only supported for integer type columns, set the current column as an auto-incrementing column.
• UNSIGNED: Only supported for integer type columns, set the current column to an unsigned integer type.
• ON UPDATE CURRENT_TIMESTAMP: Only supported by the time type, when the data in a row is updated, the time in the column is updated in real time.
• SRID: Only supported for geometry types, set the spatial reference identifier SRID.
• Character: Only supported for character type columns, set the character set of the current column.
• **Collation**: Only supported by character type columns, set the comparison rules of the current column according to the character set.
• **Invisible Column**: If there is no column suitable for the primary key in the current table, user can create the hidden column as the primary key.
• **Generated**: The value can be the calculation result of other columns. After selecting, user need to enter the **Expression** and choose whether to store the value of the column.
  • Virtual(default): The column value is calculated in real time when reading, and does not occupy storage space.
  • Stored: Save the calculation result of the column, occupying storage space.

Indexes

Set the index.

• **Name**: Enter a name for the index. If there is a primary key in the current table, the first row defaults to the primary key index information.
• **Index Type**: Select the type of index to be added.
• **Index Column**: Select the column name to which the index needs to be added.
• **Comment**: Add comment information to the current index.
• **Invisible Index**: Only supported by MySQL 8.0 and above, set the current index as a hidden index.
• **Other Options**: Add other indexing options as needed.

Foreign Keys

Set foreign key constraints.

• Name: Enter the name of the foreign key.
• **Column**: Select the foreign key column that needs to be associated with the foreign table.
• **Referenced**: Choose which library table column to associate the current foreign key column to.
• ON UPDATE: Set the limit for UPDATE operation.
• ON DELETE: Set the limit for DELETE operations.

Constraint

Set check constraints (supported by MySQL 8.0.16 and above).

• **Name**: Enter a name for the check constraint.
• **Expression**: Enter the expression for the constraint. For example: age >= 20.
• ENFORCED: Whether to enforce the constraint.

Partitioning

If the size of database is too large, it can be partitioned by column to improve performance.

• **Statement**: Enter a partition statement, please refer to the content of the **Example** on the right for input.

Table Options

Set table-level options.

• **Table Engine**: Select the storage engine for the table.
• **Character**: Set the character set of the current table.
• **Collation**: Set the comparison rules of the current table according to the character set.
• **Temporary Table**: Create a temporary table. The life cycle of the table is limited to the current connection. After the connection is closed, the temporary table will be automatically deleted.
• **Other Options**: Add other table options as needed.

4. Click Save , and in the Submit window that pops up, click OK .

   tip

   SQL scripts can be used to perform in other database clients.