Table of Contents
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 option, or by right clicking on a database within the database browser and choosing the option.
The MySQL Table Editor can be accessed from MySQL Administrator through the screen. Once you have selected a database, right click on a table and choose the option from the drop-down menu. You can also select a table and click the button to access the MySQL Table Editor.
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.
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.
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.
You can use the column editor to change the name, data type, default value, and other properties of your table's columns.
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.
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.
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.
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 option from the drop-down menu that appears.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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 button. The Confirm Table
Edit dialog will appear.
You can click the button to confirm the changes and have them applied, or click the button to cancel the changes (the table editor window is redisplayed with your changes intact). You can also click the 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 from the drop-down menu.