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.9. The Table Editor

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

The following sections discuss these tabs in further detail.

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.10. The Columns Tab

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

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.

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.

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

Figure 7.11. 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.

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.12. 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:

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.

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.13. The Triggers Tab

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

Figure 7.14. The Partitioning Tab

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

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:

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

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

Figure 7.15. The Options Tab

which are grouped into the following sections:

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:

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.

Use the Inserts tab to insert rows into the table.

To edit a row, click the field you wish to change and enter the new data. Right-clicking a row displays a menu with the following items:

Note that the insert editor features a toolbar. This has the same functionality as explained in Section 6.7.4.2, “Results Tabsheets” and Section 6.7.4.3, “Live Editing Tabsheets”. You can also hover the cursor over the toolbar to display tooltips.

Any rows you add will be inserted when you forward engineer the database (if you choose the Generate INSERT statements for tables option).

Use the Privileges tab to assign specific roles and privileges to a table. You may also assign privileges to a role using the role editor. For a discussion of this topic, see Section 7.5.5.1, “Adding Roles”.

When this tab is first opened, all roles that have been created are displayed in the list on the right. Move the roles you wish to associate with this table to the Roles list on the left. Do this by selecting a role and then clicking the < button. Use the Shift key to select multiple contiguous roles and the Control key to select noncontiguous roles.

To assign privileges to a role, click the role in the Roles list. This displays all available privileges in the Assigned Privileges list. The privileges that display are:

You can choose to assign all privileges to a specific user or any other privilege as listed previously. Privileges irrelevant to a specific table, such as the FILE privilege, are not shown.

If a role has already been granted privileges on a specific table, those privileges show as already checked in the Assigned Privileges list.