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.
Added in MySQL Workbench 5.2.45.
Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Control key to select noncontiguous 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 nonnumeric 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 nonnumeric 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.
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
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
MERGE Storage Engine.
Use the Inserts tab to insert rows into the table.
To edit a row, click the field you wish to change and enter the new data. Right-clicking a row displays a menu with the following items:
Set Field(s) to NULL: Set the column value to
Delete Row(s): Delete the selected row or rows.
Copy Row Content: Copies the row to the clipboard. Strings are
copied quoted, and
NULL values are
Copy Row Content (unquoted): Copies the row to the clipboard.
Strings are not quoted and
NULL are copied
as a space.
Copy Field Content: Copies the value of the selected field to the clipboard. Strings are quoted.
Copy Field Content (unquoted): Copies the value of the selected field to the clipboard. Strings are not quoted.
Note that the insert editor features a toolbar. This has the same functionality as explained in Section 8.2.3, “SQL Query Panel”. You can also hover the cursor over the toolbar to display tooltips.
Any rows you add will be inserted when you forward engineer the
database (if you choose the
Generate INSERT statements
for tables option).
When entering string values that 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 quotation marks, the Inserts Editor adds quoting and escapes characters that require it. However, if quoted text is entered, the Inserts Editor performs no further checks and assumes that 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.
It is possible to enter a function, or other expression, into a
field. Use the prefix
\func to prevent
MySQL Workbench from escaping quotation marks. For example, for the
md5('fred'), MySQL Workbench normally
would generate the code
prevent this, enter the expression as
md5('fred') to ensure that the quoting is not escaped.
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 184.108.40.206, “Adding Roles”.
When this tab is first opened, all roles that have been created are displayed in the list on the right. Move the roles you wish to associate with this table to the Roles list 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 Control key to select noncontiguous roles.
To assign privileges to a role, click the role in the Roles list. This displays all available privileges in the Assigned Privileges list. The privileges that display are:
You can choose to assign all privileges to a specific user or any
other privilege as listed previously. Privileges irrelevant to a
specific table, such as the
FILE privilege, are
If a role has already been granted privileges on a specific table, those privileges show as already checked in the Assigned Privileges list.
Copyright © 2006, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices