Creating a Table

Scenarios

A MySQL data table consists of basic information, field/column information, virtual columns, indexes, and foreign keys. The virtual columns, indexes, and foreign keys are optional and can be configured based on service requirements.

Procedure

1. On the top menu bar, choose Database Management. On the displayed Objects page, select Tables and click Create Table.
2. On the displayed page, specify the details under Basic Information (Table Name is mandatory).
3. Click Advanced Settings, specify table parameters based on service requirements. These parameters are mandatory. To learn more about partitioned table, see the following note. Then click Next.
   

   • Table partitioning is to divide a large table into several small tables based on conditions. Different rows in the table can be allocated to different physical partitions. Partitioned tables are not recommended because there are many constraints on MySQL partitioned tables.
   • If you need to create a partitioned table, you can create one by referring to the following example. The supported partitioning methods are RANGE, LIST, COLUMNS, KEY, and HASH.

     For example, if you want to create partitioned table employees, enter the following content for Partition Definition:

     Example for creating a partitioned table:

     CREATE TABLE employees (

     id INT NOT NULL,

     fname VARCHAR(30),

     lname VARCHAR(30),

     hired DATE NOT NULL DEFAULT '1970-01-01',

     separated DATE NOT NULL DEFAULT '9999-12-31',

     job_code INT NOT NULL,

     store_id INT NOT NULL

     )

     PARTITION BY RANGE (store_id) (

     PARTITION p0 VALUES LESS THAN (6),

     PARTITION p1 VALUES LESS THAN (11),

     PARTITION p2 VALUES LESS THAN (16),

     PARTITION p3 VALUES LESS THAN (21)

     );

     Partition definition:

     PARTITION BY RANGE (store_id) (

     PARTITION p0 VALUES LESS THAN (6),

     PARTITION p1 VALUES LESS THAN (11),

     PARTITION p2 VALUES LESS THAN (16),

     PARTITION p3 VALUES LESS THAN (21)

     )

4. On the Column page, click Add and set Column Name, Type, Length, Nullable, Primary Key, Comment, and Extended Information as needed.
   • If you do not need to add virtual columns, indexes, or foreign keys, click Create at the bottom of the page. In the displayed SQL Preview dialog box, click Execute.
   

   • Only MySQL 5.6.5 and later support default DATETIME values.
   • In versions earlier than MySQL 5.6.5, leave default values blank. Otherwise, an error occurs.
   • If you need to add virtual columns, indexes, and foreign keys, click Next. Then, set the column name, type, length, nullable, primary key, expression, storage type, comment, and extended information. If you need to set the table index or foreign key, click Next. After the setting, click Create.
   

   When you create a foreign key, the type of columns in the referenced table must be the same as that of included columns, and must be the primary key or have a unique index.

5. In the SQL Preview dialog box, click Execute to create a table.

• The length of a column name is limited. Enter no more than 64 characters for the MySQL engine.
• In the Type column, you can select only the parameters from the drop-down list box.
• In the Length column, you can change the length for some types of columns.
• If Primary Key is selected, Nullable will be grayed out.
• Auto Increment can be set for one column only. When it is selected, Primary Key must be selected, and a default value cannot be set.