7.3.1.3 The MySQL Table Editor

7.3.1.3.1 The Main Editor Window
7.3.1.3.2 The Columns Tab
7.3.1.3.3 The Indexes Tab
7.3.1.3.4 The Foreign Keys Tab
7.3.1.3.5 The Triggers Tab
7.3.1.3.6 The Partitioning Tab
7.3.1.3.7 The Options Tab

The MySQL Table Editor is a component that enables the creation and modification of tables. You can add or modify a table's columns or indexes, change the engine, add foreign keys, or alter the table's name.

The MySQL Table Editor can be accessed in several ways, and most commonly by right-clicking on a table name within the Object Viewer and choosing ALTER TABLE. This will open a new tab within the main SQL Editor window. You can also access the MySQL Table Editor from an EER Diagram by double-clicking a table object.

7.3.1.3.1 The Main Editor Window

Any number of tables may be edited in the MySQL Table Editor at any one time. Adding another table creates a new tab at the top of the editor. By default, the MySQL Table Editor appears docked at the top of the table editor tab, within the SQL editor..

The MySQL Table Editor is shown on top of the following figure.

Figure 7.10 The Table Editor

The table editor

The MySQL Table Editor provides a work space that has tabs used to perform these actions:

  • Columns: Add or modify columns

  • Indexes: Add or modify indexes

  • Foreign Keys: Add or modify foreign keys

  • Triggers: Add or modify triggers

  • Partitioning: Manage partitioning

  • Options: Add or modify various general, table, and row options

The following sections discuss these tabs in further detail.

7.3.1.3.2 The Columns Tab

Use the Columns tab to display and edit all the column information for a table. With this tab, you can add, drop, and alter columns.

You can also use the Columns tab to change column properties such as name, data type, and default value.

Figure 7.11 The Columns Tab

The columns tab

Right-click a row under the Column Name column to open a pop-up menu with the following items:

  • Move Up: Move the selected column up.

  • Move Down: Move the selected column down.

  • Copy: Copies the column for a model.

  • Cut: Copies and then deletes the column for a model.

  • Paste: Pastes the column. If a column with the same name already exists, then _copy1 is appended to the column name.

  • Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Control key to select separated columns.

  • Refresh: Update all information in the Columns tab.

  • Clear Default: Clear the assigned default value.

  • Default NULL: Set the column default value to NULL.

  • Default 0: Set the column default value to 0.

  • Default CURRENT_TIMESTAMP: Available for TIMESTAMP data types.

  • Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP: Available for TIMESTAMP data types.

To add a column, click the Column Name field in an empty row and enter an appropriate value. Select a data type from the Datatype list. Select the column property check boxes as required according to the list of column properties below, and also read the CREATE TABLE documentation for information about what these options mean.

  • PK: PRIMARY KEY

  • NN: NOT NULL

  • UQ: UNIQUE INDEX

  • BIN: BINARY

  • UN: UNSIGNED

  • ZF: ZEROFILL

  • AI: AUTO_INCREMENT

To change the name, data type, default value, or comment of a column, double-click the value you wish to change. The content then becomes editable.

You can also add column comments to the Column Comment field. It is also possible to set the column collation, using the list in the Column Details panel.

To the left of the column name is an icon that indicates whether the column is a member of the primary key. If the icon is a small key, that column belongs to the primary key, otherwise the icon is a blue diamond or a white diamond. A blue diamond indicates the column has NN set. To add or remove a column from the primary key, double-click the icon. You can also add a primary key by checking the PRIMARY KEY check box in the Column Details section of the table editor.

If you wish to create a composite primary key you can select multiple columns and check the PK check box. However, there is an additional step that is required, you must click the Indexes tab, then in the Index Columns panel you must set the desired order of the primary keys.

Note

When entering default values, in the case of CHAR and VARCHAR data types MySQL Workbench will attempt to automatically add quotation marks, if the user does not start their entry with one. For other data types the user must manage quoting if required, as it will not be handled automatically by MySQL Workbench.

Caution

Care must be taken when entering a default value for ENUM columns because a non-numeric default will not be automatically quoted. You must manually add single quote characters for the default value. Note that MySQL Workbench will not prevent you from entering the default value without the single quotation marks. If a non-numeric default value is entered without quotation marks, this will lead to errors. For example, if the model is reverse engineered, the script will contain unquoted default values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.

Note

ENUM, BIT, and SET must contain at least one value when entering these data types into MySQL Workbench.

7.3.1.3.3 The Indexes Tab

The Indexes tab holds all index information for your table. Use this tab to add, drop, and modify indexes.

Figure 7.12 The Indexes Tab

The indexes tab

Select an index by right-clicking it. The Index Columns section displays information about the selected index.

To add an index, click the last row in the index list. Enter a name for the index and select the index type from the list. Select the column or columns that you wish to index by checking the column name in the Index Columns list. You can remove a column from the index by removing the check mark from the appropriate column.

You can also specify the order of an index by choosing ASC or DESC under the Order column. Create an index prefix by specifying a numeric value under the Length column. You cannot enter a prefix value for fields that have a data type that does not support prefixing.

To drop an index, right-click the row of the index you wish to delete, then select the Delete Selected Indexes menu item.

7.3.1.3.4 The Foreign Keys Tab

The Foreign Keys tab is organized in much the same fashion as the Indexes tab and adding or editing a foreign key is similar to adding or editing an index.

Figure 7.13 The Foreign Keys Tab

The foreign keys tab

To add a foreign key, click the last row in the Foreign Key Name list. Enter a name for the foreign key and select the column or columns that you wish to index by checking the column name in the Column list. You can remove a column from the index by removing the check mark from the appropriate column.

Under Foreign Key Options, choose an action for the update and delete events. The options are:

  • RESTRICT

  • CASCADE

  • SET NULL

  • NO ACTION

To drop a foreign key, right-click the row you wish to delete, then select the Delete Selected FKs menu item.

To modify properties of a foreign key, select it and make the desired changes.

7.3.1.3.5 The Triggers Tab

The Triggers tab opens a field for editing an existing trigger or creating a new trigger. Create a trigger as you would from the command line.

Figure 7.14 The Triggers Tab

The triggers tab

7.3.1.3.6 The Partitioning Tab

To enable partitioning for your table, check the Enable Partitioning check box. This enables the partitioning options.

Figure 7.15 The Partitioning Tab

The partitioning tab

The Partition By pop-up menu displays the types of partitions you can create:

  • HASH

  • LINEAR HASH

  • KEY

  • LINEAR KEY

  • RANGE

  • LIST

Use the Parameters field to define any parameters to be supplied to the partitioning function, such as an integer column value.

Choose the number of partitions from the Partition Count list. To manually configure your partitions, check the Manual check box. This enables entry of values into the partition configuration table. The entries in this table are:

  • Partition

  • Values

  • Data Directory

  • Index Directory

  • Min Rows

  • Max Rows

  • Comment

Subpartitioning is also available. For more information about partitioning, see Partitioning.

7.3.1.3.7 The Options Tab

The Options tab enables you to set several types of options.

Figure 7.16 The Options Tab

The options tab

which are grouped into the following sections:

  • General Options

  • Row Options

  • Storage Options

  • Merge Table options

The following discussion describes these options in more detail.

General Options Section

In the General Options section, choose a pack keys option. The options are Default, Pack None, and Pack All. You may also encrypt the definition of a table. The AUTO_INCREMENT and delayed key update behaviors apply only to MyISAM tables.

Row Options Section

To set the row format, choose the desired row format from the list. For more information about the different row formats that are available, see MyISAM Table Storage Formats.

These options are:

  • Default

  • Dynamic

  • Fixed

  • Compressed

  • Redundant

  • Compact

When you expect a table to be particularly large, use the Avg. Row, Min. Rows, and Max. Rows options to enable the MySQL server to better accommodate your data. See CREATE TABLE Syntax for more information on how to use these options.

Storage Options Section

The Storage Options section is available only for MyISAM tables. Use it to configure a custom path to the table storage and data files. This can help improve server performance by locating different tables on different hard drives.

Merge Table Options Section

Use the Merge Table Options section to configure MERGE tables. To create a MERGE table, select MERGE as your storage engine and then specify the MyISAM tables you wish to merge in the Union Tables dialog.

You may specify the action the server should take when users attempt to perform INSERT statements on the merge table. You may also select the Merge Method by selecting from the list. For more information about MERGE tables, see The MERGE Storage Engine.