Any number of tables may be edited in the MySQL Table Editor at any one time. Adding an additional table creates a new tab at the top of the editor. By default the MySQL Table Editor appears docked at the bottom of the application.

The MySQL Table Editor is shown in the following figure.

Figure 7.8. The Table Editor

The MySQL Table Editor consists of a work space divided into the following tabs:

Each of these tabs is discussed in further detail in the following sections.

Use this tab to edit the table name or add a comment to the table. Easily change the collation or the table engine using drop down list boxes.

The Columns tab is used to display and edit all the column information for a table. Using this tab, you can add, drop, and alter columns.

You can also use the column tab to change the name, data type, default value, and other properties of your table's columns.

Figure 7.9. The Columns Tab

To add a column simply click the Column Name field in an empty row and enter an appropriate value. Select a data type from the Datatype drop down list box. Select a column property checkbox as required according to the following list of column properties:

Right clicking a row under the Column Name column opens a pop-up window with the following options:

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

You can also add column comments to the Column Comment text area. It is also possible to set the column collation, using the listbox 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 on the icon. You can also add a primary key by checking the PRIMARY KEY checkbox 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 checkbox. However, there is an additional step that is required, you will need to click the Indexes tab, then in the Index Columns panel you need to set the desired order of the primary keys.

The Indexes tab holds all index information for your table. You can add, drop, and modify indexes using this tab.

Figure 7.10. The Indexes Tab

Select an index by right clicking it. Doing this displays information about the index in the Index Columns section.

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 drop down list box. 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 and then select the Delete Selected Indexes menu option.

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.

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 and then select the Delete Selected FKs menu option.

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

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

If you wish to enable partitioning for your table check the Enable Partitioning check box. Doing this enables the partitioning options.

The Partition By drop down list box displays the types of partitions you can create. These are:

Use the Parameters text box to define the parameter(s) that will be supplied to the partitioning function, an integer column value for example.

Choose the number of partitions from the Partition Count drop down list box. If you wish to manually configure your partitions check the Manual check box. Doing this enables entry of values into the partition configuration table. The entries in this table are:

Subpartitioning is not yet enabled. For more information about partitioning see Partitioning.

The Options tab allows you to set the general options and row options.

In the General Options frame, 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.

To set the row format, choose the desired row format from the drop-down list. See MyISAM Table Storage Formats for more information about the different row formats that are available. This only applies to MyISAM tables.

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.

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

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

You can also specify the action the server should take when users attempt to perform INSERT statements on the merge table. See The MERGE Storage Engine for more information about MERGE tables. Again, this only applies to MyISAM tables. You may also select the Merge Method by selecting from the drop down list box.

Use the Inserts tab to insert records into the table. Clicking the Open Editor ... button on the lower right hand side opens the Standard Inserts editor. Use this editor to add records to the table.

When you have finished adding records, press OK. The records you have added are displayed in the Inserts tab. Reopening the editor displays all the records shown in the Inserts tab. To edit a record simply click on the field you wish to change and enter the new data. To delete a record, click the button on the left beside the desired row and then press the Delete key. You can select and delete all records by clicking in the top left hand column of the editor and then pressing the Delete key. Your changes will not be applied until you press the OK button. To back out of an operation, press the Cancel button.

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

Note when entering string values there is slightly different behavior between the 5.0, 5.1 and 5.2 versions of MySQL Workbench.

For 5.0 and 5.1 if a string is entered without leading and trailing quotes, the Inserts Editor adds quoting and escapes characters that require it. However, if quoted text is entered, the Inserts Editor carries out no further checks since it assumes a correctly escaped and quoted sequence has been entered.

5.2 features a new Inserts Editor. In this case the user enters the string without quoting or escaping and the Inserts Editor takes care of all quoting and escaping as required.

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.3.3.1, “Adding Roles”.

When this tab is first opened, all the roles that have been created are displayed in the list box on the right. Move the roles you wish to associate with this table to the Roles list box 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 Ctrl key to select noncontiguous roles.

To assign privileges to a role click on a role in the Roles list box. Doing this displays all available privileges in the Assigned Privileges list box. The privileges that display are:

You can choose to assign all privileges to a specific user or any other privilege listed in the preceding. Privileges irrelevant to a specific table, the FILE privilege for example, 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 box.