The MySQL Table Editor is a component that enables the creation and modification of tables. You can add or modify a table's columns or indexes, change the engine, add foreign keys, or alter the table's name.
The MySQL Table Editor can be accessed in several ways, and most commonly by right-clicking on a table name within the Object Viewer and choosing ALTER TABLE. This will open a new tab within the main SQL Editor window. You can also access the MySQL Table Editor from an EER Diagram by double-clicking a table object.
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.
The MySQL Table Editor provides a work space that has tabs used to perform these actions:
Columns: Add or modify columns
Indexes: Add or modify indexes
Foreign Keys: Add or modify foreign keys
Triggers: Add or modify triggers
Partitioning: Manage partitioning
Options: Add or modify various general, table, and row options
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.
Right-click a row under the
Column Name column
to open a pop-up menu with the following items:
Move Up: Move the selected column up.
Move Down: Move the selected column down.
Copy: Copies the column for a model.
Cut: Copies and then deletes the column for a model.
Paste: Pastes the column. If a
column with the same name already exists, then
_copy1 is appended to the column name.
Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Control key to select separated columns.
Refresh: Update all information in the Columns tab.
Clear Default: Clear the assigned default value.
Default NULL: Set the column
default value to
Default 0: Set the column default
TIMESTAMP data types.
Default CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP: Available for
TIMESTAMP data types.
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.
PK: PRIMARY KEY
NN: NOT NULL
UQ: UNIQUE INDEX
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
Comment field. It is also possible to set the column
collation, using the list in the Column
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
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.
When entering default values, in the case of
types MySQL Workbench will attempt to automatically add quotation
marks, if the user does not start their entry with one. For
other data types the user must manage quoting if required, as it
will not be handled automatically by MySQL Workbench.
Care must be taken when entering a default value for
ENUM columns because a non-numeric default
will not be automatically quoted. You must manually add single
quote characters for the default value. Note that MySQL Workbench
will not prevent you from
entering the default value without the single quotation marks.
If a non-numeric default value is entered without quotation
marks, this will lead to errors. For example, if the model is
reverse engineered, the script will contain unquoted default
ENUM columns and will fail if an
attempt is made to run the script on MySQL Server.
ENUM, BIT, and SET must contain at least one value when entering these data types into MySQL Workbench.
The Indexes tab holds all index information for your table. Use this tab to add, drop, and modify indexes.
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
DESC under the
Order column. Create an index prefix by
specifying a numeric value under the
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.
To add a foreign key, click the last row in the
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
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.
To enable partitioning for your table, check the Enable Partitioning check box. This enables the partitioning options.
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.
which are grouped into the following sections:
Merge Table options
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
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
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
Storage Options section is available only
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
Merge Table Options Section
Merge Table Options section to
MERGE tables. To create a
MERGE table, select
your storage engine and then specify the
tables you wish to merge in the Union Tables
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
Method by selecting from the list. For more information
MERGE tables, see
The MERGE Storage Engine.