Chapter 24. The MySQL Table Editor

Table of Contents

24.1. Introduction
24.2. The Main Editor Window
24.3. The Columns And Indices Tab
24.3.1. The Column Editor
24.3.2. The Detail Area
24.4. The Table Options Tab
24.5. The Advanced Options Tab
24.5.1. The Various Section
24.5.2. The Row Options Section
24.5.3. The Storage Options Section
24.5.4. The Merge Table Options Section
24.5.5. The Table RAID Settings Section
24.6. Applying Your Changes

24.1. Introduction

The MySQL Table Editor is a component of the MySQL Query Browser and the MySQL Administrator enabling the visual creation and modification of tables.

The MySQL Table Editor can be accessed from the MySQL Query Browser by right clicking on a table within the database browser and choosing the Edit Table option, or by right clicking on a database within the database browser and choosing the Create New Table option.

The MySQL Table Editor can be accessed from MySQL Administrator through the Catalogs screen. Once you have selected a database, right click on a table and choose the Edit Table option from the drop-down menu. You can also select a table and click the Edit Table button to access the MySQL Table Editor.

The appearance of the MySQL Table Editor varies slightly depending upon the operating system (OS) and whether the MySQL Table Editor is docked or not. For example, under Linux the Columns and Indexes are separate tabs but under Windows they are combined in one. However, the same basic functionality is available regardless of docking or the particular OS.

Note

Docking is not supported on all operating systems.

24.2. The Main Editor Window

The MySQL Table Editor consists of a work space divided into tabs, some general information prompts, and two or three action buttons.

Regardless of the active tab, you can always edit the table name and the table comment.

Figure 24.1. The MySQL Table Editor

The MySQL Table Editor

The tabbed area is divided into three sections:

  • Columns and Indices: Use the Columns and Indices tab to create and modify the table's column and index information. You can also create FOREIGN KEY relationships using this tab.

  • Table Options:Use the Table Options tab to choose the storage engine and default character set used by the table.

  • Advanced Options: Use the Advanced Options tab to configure options such as the per-table storage directory, MERGE and RAID table options, and table/row length options.

Each of these areas are discussed in further detail in the following sections.

24.3. The Columns And Indices Tab

The Columns and Indices tab can be used to display and edit all column and index information for your table. Using this tab, you can add, drop, and alter columns and indexes.

24.3.1. The Column Editor

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

Figure 24.2. The Column Editor

The column editor

To add a column simply double-click a column in an empty row and enter an appropriate value.

To change the name, data type, default value, or comment of a column, double click on the value you wish to change. The value becomes editable and you can complete your changes by pressing the Enter key.

To modify the flags on a column (UNSIGNED, BINARY, ASCII, and so on) check and uncheck the boxes corresponding to the flag you wish to change.

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. To add or remove a column from the primary key, you can click on the icon.

24.3.2. The Detail Area

The detail area of the Columns and Indices tab is divided into three tabs used for modifying the index, foreign key, and miscellaneous information regarding your columns.

24.3.2.1. The Indices Tab

The Indices tab holds all index information for your table. You can add, drop, and modify indexes using the indices tab, in combination with the column editor.

Figure 24.3. The Index Editor

The index editor

To add an index, click the + (plus) button below the index list. The MySQL Query Browser prompts for an index name and the new index is created. Under Windows, you can change the automatically generated index name by using the Options dialog window. For more information, see Section 23.4, “Editors”.

To drop an index, select the index and click the (minus) button.

Use the Index Name and Index Kind dialog boxes to modify the name and type (UNIQUE, FULLTEXT, and so on) of the index.

To add columns to an index, either click and drag the column to the Index Columns box or select the column you wish to add and click the + (plus) button to the right of the Index Columns box. You can remove a column from the index by selecting the column and clicking the (minus) button to drop the column from the index.

To change an index to only refer to a column prefix (such as with the CHAR and VARCHAR string data types), select the index column you wish to prefix in the Index Columns box and then click the arrow icon to the right of the Index Columns box. Select the Set Index Column Length option from the drop-down menu that appears.

24.3.2.2. The Foreign Keys Tab

The Foreign Keys tab is divided into two sections, one with a list of foreign keys and one with various dialog boxes for foreign key settings.

To add a foreign key, click the + (plus) button below the foreign key list. A dialog box prompts for a foreign key name and the new foreign key is created. Under Windows, you can change the automatically generated foreign key name by using the Options dialog window. For more information, see Section 23.4, “Editors”.

To drop a foreign key, select the foreign key and click the (minus) button below the foreign key list.

You can modify the name of the foreign key, its ON DELETE, and its ON UPDATE actions using the dialog boxes provided in the Foreign Key Settings section of the tab.

The options are:

  • NO ACTION

  • RESTRICT

  • CASCADE

  • SET NULL

To establish a foreign key relationship, choose a table from the Ref. Table drop-down list. (Under Linux this list box is called Refer. Table.) The fields that can be related to create a foreign key are listed in the frame below the drop-down list. This frame contains two columns; one labeled Column and the other Reference Column. To select a column as a foreign key, double click the area below Column and select a column from the drop-down list. Alternately, drag a column from the column editor to the Column section. To add a related column from the referenced table, double click the area below Reference Column and select the related column from the drop-down list.

24.3.2.3. The Column Details Tab

The Column Details tab provides an interface for setting the parameters of a column without using the table interface of the column editor.

All settings that are available in the Column Editor are also available in the Column Details tab, and in addition you can also configure the column character set and column default collation from the Column Details tab.

Depending upon circumstances one or the other of these views may be more convenient. However, changing the data type in the tabular view is especially easy because a drop-down list shows all available data types.

24.4. The Table Options Tab

The Table Options tab allows you to change the storage engine and default character set of your table.

The potential storage engines are listed in a drop-down list box. Select a particular engine to display a brief summary of that engine's features and capabilities.

Under Windows, engines that are not available are shown in red. Under Mac OS X, unavailable engines are grayed out and under Linux there is no differentiation. If your server has built-in support for the Falcon engine, that engine will show up in the list of available engines. Otherwise, the Falcon engine does not show at all.

To change the storage engine for your table, select the desired storage engine from the list box.

To change the default character set or collation of your table, choose a new option from the drop-down list of available character sets.

24.5. The Advanced Options Tab

The Advanced Options tab is used to configure table options that would be considered outside the standard set of options that most users designate when creating and modifying tables.

The Advanced Options tab is divided into several sub-sections, each of which is described in the upcoming sections of this manual.

For descriptions of most options set using the Advanced Options tab, see CREATE TABLE Syntax.

24.5.1. The Various Section

The Various section of the Advanced Options tab contains options for you to set the PACK KEYS behavior, the table password, the initial AUTO_INCREMENT value, and the delayed key update behavior.

The AUTO_INCREMENT and delayed key update behaviors apply only to MyISAM tables.

24.5.2. The Row Options Section

The Row Options section can be used to configure options such as the row format, checksum use, and the row size parameters needed for large tables.

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

When you expect a table to be particularly large, use the Avg Row Length, 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.

24.5.3. The Storage Options Section

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 and is not available for servers running under the Windows operating system.

24.5.4. The Merge Table Options Section

The Merge Table Options section is used to configure MERGE tables in MyISAM. To create a MERGE table, select MERGE as your storage engine in the Table Options Tab 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 on MERGE tables.

24.5.5. The Table RAID Settings Section

The Table RAID Settings section allows you to configure RAID support for MyISAM tables. RAID allows MyISAM table data files to grow larger than the 2GB/4GB size limit imposed by some operating systems.

For more information on using RAID support with MyISAM, see CREATE TABLE Syntax.

24.6. Applying Your Changes

The changes you make with the MySQL Table Editor are not immediately applied but are instead queued to be applied in batches after you have made all your edits.

To apply the changes you have made, click the Apply Changes button. The Confirm Table Edit dialog will appear.

Figure 24.4. The Confirm Table Edit Dialog

The Confirm Table Edit dialog

You can click the Execute button to confirm the changes and have them applied, or click the Cancel button to cancel the changes (the table editor window is redisplayed with your changes intact). You can also click the Discard Changes button in the main MySQL Table Editor window to discard all changes you have made.

You can also copy the proposed changes to the clipboard for further editing by highlighting the ALTER TABLE or CREATE TABLE statement, right-clicking and choosing Copy from the drop-down menu.