Chapter 8. The MySQL Table Editor

Table of Contents

8.1. Introduction
8.2. The Main Editor Window
8.3. The Columns And Indices Tab
8.3.1. The Column Editor
8.3.2. The Detail Area
8.4. The Table Options Tab
8.5. The Advanced Options Tab
8.5.1. The Various Section
8.5.2. The Row Options Section
8.5.3. The Storage Options Section
8.5.4. The Merge Table Options Section
8.5.5. The Table RAID Settings Section
8.6. Applying Your Changes

8.1. Introduction

The MySQL Table Editor is a component of both the MySQL Query Browser and 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.

8.2. The Main Editor Window

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

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

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

8.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.

8.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 8.2. The column editor

The column editor

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.

8.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.

8.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 8.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. 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.

8.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. The MySQL Query Browser prompts for a foreign key name and the new foreign key is created.

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.

To establish a foreign key relationship, choose a table from the Ref. Table drop-down list. The columns that can be referenced as foreign keys are listed in the area below, which has two sections. To add a column (of the table you are editing) you can either double click the area below Column and then select a column from the drop-down list, or drag a column from the column editor to the Column section. To add a column (of the reference table) double click the area below Reference Column and then select a column from the drop-down list.

8.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.

8.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, along with a brief summary of each storage engine's features and strengths.

To change the storage engine for your table, click on the radio button next to the desired storage engine.

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

8.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.

8.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.

8.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.

8.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.

8.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.

8.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.

8.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 8.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.