Use this menu item to open a project, begin a new project, or save a project. Choosing New Model opens the default schema, mydb. Choosing Open Model opens a file dialog box with the default file type set to MySQL Workbench Models (MWB). To display a list of recently opened MWB files, choose the Open Recent menu option. The keyboard command to create a new project is Ctrl N and the command to open an existing project is Ctrl O.

To close the currently active MySQL Model or EER Diagram tab, use the Close Tab option. You can also do this from the keyboard by pressing Ctrl W. To reopen the MySQL Model tab, see Section 7.5.1.3, “The View Menu”. To reopen an EER Diagram tab, double-click the EER Diagram icon in the EER Diagrams section of the MySQL Model page.

Use the Save Model or Save Model As menu options to save a model. When you save a model its name appears in the title bar of the application. If you have made changes to a project and have not saved those changes, an asterisk appears in the title bar following the model name. When you save a model it is saved as a MySQL Workbench file with the extension mwb.

Use the Import menu option to import a MySQL data definition (DDL) script file, one created by issuing the command mysqldump --no-data, for example. If the script does not contain a CREATE db_name; statement, the schema objects will be copied to the default schema, mydb. If the script creates a database, a new tab bearing the database name is added to the Physical Schemata section of the MySQL Model page. If the script contains data, it will be ignored. Importing a DDL script is discussed in detail in Section 7.7.9.1, “Reverse Engineering Using a Create Script”.

Under the Import menu option you can also import DBDesigner4 files.

There are variety of options under the Export menu item. You may generate the SQL statements necessary to create a new database or alter an existing one. These menu items are discussed in detail in Section 7.7.10.1, “Forward Engineering Using SQL Scripts”.

Using the Export menu item you can also export an EER diagram as a PNG, SVG, PDF or Postscript file. For an example of a PNG file see Figure 7.45, “The sakila EER Diagram”.

The Page Setup menu item enables you to set the paper size, orientation and margins for printing purposes.

The print options are only enabled if the EER Diagrams tab is selected. You have the choice of printing your model directly to your printer, printing it as a PDF file, or creating a PostScript file. For more information see Section 7.9, “Printing”.

Use the Document Properties menu option to set the following properties of your project:

Under this menu item find the options for cutting, copying, and pasting. These actions can also be performed using the Ctrl X, Ctrl C, and Ctrl V key combinations. Undo a deletion using the Undo Delete 'object_name' option. The Ctrl Z key combination can also be used to undo an operation. It is also possible to carry out a Redo operation using either the menu item, or the key combination Ctrl Y.

Also find a Delete 'object_name' menu item for removing the currently selected object. The text description for this menu item changes to reflect the name of the currently selected object. The keyboard command for this action is Ctrl Delete. You can also right click an object and choose the delete option from the pop-up menu.

The Delete 'object_name' menu item behaves differently depending upon circumstances. For instance, if an EER Diagram is active and a table on the canvas is the currently selected object, a dialog box may open asking whether you want to remove the table from the canvas only or from the database as well. For setting the default behavior when deleting from an EER Diagram see Section 5.4.4, “The Model Tab”.

Choose Edit Selected to edit the currently selected object. You can also perform edits in a new window by selecting Edit Selected in New Window. The keyboard shortcut for Edit Selected is Ctrl E and Ctrl Shift E for Edit Selected in New Window.

The Select option has the following submenus:

These menu items are only active when an EER Diagram tab is selected. The Similar Figures and the Connected Figures menu options are disabled if no object is currently selected on an EER diagram.

When multiple objects have been selected using one of these menu options, you can navigate between selected items by choosing the Go to Next Selected or Go to previous Selected menu options.

Selecting items changes some of the Edit menu options. If only one object is selected, that object's name appears after the Cut, Copy and Delete menu options. If more than one object is selected, these menu items show the number of objects selected.

7.5.1.2.1. Find Dialog Window

The Find menu item displays a sub-menu with the following menu items:

• Find: Takes you to the toolbar search box. You can look for objects in the current view. Find can locate objects in the Model view, the EER Diagram view, and also in the Catalog palette.

• Find Next: Finds the next occurrence of the object.

• Find Previous: Finds the previous occurrence of the object.

• Search and Replace: Displays the Search and Replace dialog. This is currently only for use with the SQL Editor, to allow you to quickly search and replace script code items.

The Standard Edition of MySQL Workbench includes a more advanced Find facility:

Figure 7.2. The Find Window

You can search the following locations:

• Entire Model: Search the entire model.

• Current View: Search the current view only. This may be the MySQL Model page.

• All Views: Search the MySQL Model Page and all EER diagrams.

• Database Objects: Search database objects only.

• Selected Figures: Search the currently selected objects. This feature only works for EER diagrams.

Enter the text you wish to search for in the Find Text drop down list box. You may also select any or all of the following check boxes:

• Match Case

• Whole Word

• Use Regular Expression

• Search in Comments

• Search in SQL for Views, SPs etc.

Any text you enter into the Find Text drop down list box is retained for the duration of your session. Use the Next or Previous buttons to find occurrences of your search criterion.

Clicking the Find All button opens a Find Results window anchored at the bottom of the application. If you wish, you may undock this window as you would any other.

Use this window to navigate to objects. For example, double clicking the Description of an object located on an EER diagram navigates to the specific diagram and selects the object. Notice that the properties of the object are displayed in the Properties palette.

The Find dialog window can also be opened using the Ctrl F key combination. Use Ctrl G to find the next occurrence and Ctrl Shift G to find a previous occurrence. Close the Find dialog window by clicking the x in the top right corner or by pressing the Esc key.

The Options available under this menu item are:

The Arrange menu option applies only to objects on an EER diagram canvas and is only visible if an EER diagram view is active. The options under this menu item are as follows:

The menu options available under the Model menu item are as follows:

There are several options under the Database menu option:

The Plugins menu option lists any plugins that you may have installed. For more information about this menu option see Section 9.3, “Plugins”.

This menu currently has three items:

The Community menu option offers the following choices:

Use these menu options to go online and learn more about MySQL Workbench.

The Help menu option offers the following choices:

Use these menu options to go online and learn more about MySQL Workbench.

read_mysql_cfg_file  C:\Program Files\MySQL\MySQL Server 5.1\my.ini
[('tmp_table_size', '9M'), 
('myisam_sort_buffer_size', '18M'), 
('table_cache', '256'), 
('read_rnd_buffer_size', '256K'), 
('port', '3306'), ('max_connections', '100'), 
('innodb_buffer_pool_size', '18M'), 
('myisam_max_sort_file_size', '100G'), 
('sql-mode', '"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"'), 
('basedir', '"C:/Program Files/MySQL/MySQL Server 5.1/"'), 
('default-character-set', 'latin1'), 
('datadir', '"C:/ProgramData/MySQL/MySQL Server 5.1/Data/"'), 
('innodb_log_buffer_size', '1M'), 
('innodb_log_file_size', '10M'), 
('innodb_thread_concurrency', '8'), 
('read_buffer_size', '64K'), 
('innodb_additional_mem_pool_size', '2M'), 
('thread_cache_size', '8'), 
('innodb_flush_log_at_trx_commit', '1'), 
('query_cache_size', '0'), 
('sort_buffer_size', '256K'), 
('default-storage-engine', 'INNODB'), 
('key_buffer_size', '11M')]
MySQL Workbench OSS for Windows version 5.2.8
Cairo Version: 1.8.6
Rendering Mode: GDI requested (create a diagram to confirm)
OpenGL Driver Version: Not Detected
OS: unknown
CPU: Intel(R) Core(TM)2 Duo CPU     T9300  @ 2.50GHz, 1.0 GB RAM
Video adapter info:
Adapter type: VirtualBox Graphics Adapter
Chip Type: VBOX
BIOS String: Version 0xB0C2 or later
Video Memory: 12288 KB

The toolbar also changes depending upon which tool from the vertical toolbar is active. These tools are discussed in Section 7.6.1, “The Vertical Toolbar”.

If the Table tool is active, drop down list boxes of schemata, engine types and collations appear on the toolbar. The table properties can then be modified using the Properties Editor.

When an object is selected the object's properties, such as color, can be changed in the Properties Editor.

The Physical Schemata panel is divided up into the following sections:

Each section contains the specified database objects and an icon used for creating additional objects.

Any database objects added to an EER diagram canvas also show up in the Physical Schemata section. For information about adding objects to an EER diagram canvas see Section 7.6, “EER Diagram Editor”.

To add a role, double-click the Add Role icon. Doing this creates a role with the default name role1. Right-clicking a role opens a pop-up menu with the following options:

To rename a role, simply click the role name and you will then be able to edit the text.

All roles that have been defined are listed under Roles on the left side of the role editor. Double-clicking a role object opens the role editor docked at the bottom of the screen.

Figure 7.4. Role Editor

Select the role that you wish to add objects to. You may drag and drop objects from the Physical Schemata to the Objects section of the role editor. To assign privileges to a role select a role from the Roles section and then select an object in the Objects section. In the Privileges section check the rights you wish to assign to this role. For example, a web_user role might have only SELECT privileges and only for database objects exposed through a web interface. Creating roles can make the process of assigning rights to new users much easier.

To add a user double-click the Add User icon. Doing this creates a user with the default name user1. Double-clicking this user opens the user editor docked at the bottom of the application.

In the User Editor, set the user's name using the Name text box and set the password using the Password text box. Assign one role or a number of roles to the user by selecting the desired roles from the text area on the right and then clicking the < button. Roles may be revoked by moving them in the opposite direction.

Right-clicking a user opens a pop-up menu. These options function as described in Section 7.5.5.1, “Adding Roles”.

In some circumstances you may want to make an object on an EER diagram invisible. To do this, select the object and, in the Properties palette, set the visible property to False.

The Layer palette provides an easy way to locate an object, such as a relationship, that has been set to hidden. Open the Layers palette and select the object by double-clicking it. You can then edit the object and change its visibility setting to Fully Visible.

The standard mouse pointer, located at the top of the vertical toolbar, is the default mouse pointer for your operating system. Use this tool to revert to the standard mouse pointer after using other tools.

From the keyboard, use the Esc key to revert to the default pointer.

The hand tool is used to move the entire EER diagram. Left-click on this tool and then left-click anywhere on the EER diagram canvas holding down the mouse button. Moving the mouse changes the view port of the canvas.

To determine your position on the canvas look at the Model Navigator panel on the upper right. If the Model Navigator panel is not open, use the View, Windows, Model Navigator to open it.

From the keyboard, use the H key to activate this tool.

You can also change the view port of an EER diagram using the Model Navigator panel. To do this see Section 7.5.8, “The Model Navigator Panel”.

Use the eraser tool to delete objects from the EER Diagram canvas.

Change the mouse pointer to the eraser tool and click the object you wish to delete. Depending upon your settings, the delete dialog box should open, asking you to confirm the type of deletion.

From the keyboard, use the D key to activate this tool.

In addition to using the eraser tool, you can also delete an object by selecting it and pressing Ctrl Delete or right-clicking it and choosing Delete from the pop up menu.

The layer tool is the rectangular icon with a capital L in the lower left corner.

The layer tool is used to organize the objects on an EER Diagram canvas. It is useful for grouping together similar objects. You may, for instance, use it to group all your views together.

Click the layer tool and use it to draw a rectangle on the canvas. Change to the standard mouse pointer tool and pick up any objects you would like to place on the newly created layer.

To change the size of a layer, first select it by clicking it. When a layer is selected small rectangles appear at each corner and in the middle of each side. Adjust the size by dragging any one of these rectangles.

You can also make changes to a layer by selecting the layer and changing properties in the Properties panel. Using the Properties panel is the only way to change the name of a layer.

From the keyboard, use the L key to activate this tool. For more information about layers see Section 7.7.5, “Creating Layers”.

The text tool is the square icon with a capital N in the top left corner. Use this tool to place text objects on the EER diagram canvas. Click the tool and then click the desired location on the canvas. Once a text object has been dropped on the canvas, the mouse pointer reverts to its default.

To add text to a text object, right-click the text object and choose either of the pop-up menu options, Edit Note ... or Edit in New Window ....

You can manipulate the properties of a text object by selecting it and then changing its properties in the Properties panel.

From the keyboard, use the N key to activate this tool. For more information about text objects see Section 7.7.7, “Creating Text Objects”.

Use the image tool to place an image on the canvas. When this tool is selected and you click the canvas, a dialog box opens allowing you to select the desired graphic file.

From the keyboard, use the I key to activate this tool. For more information about images see Section 7.7.8, “Creating Images”.

Use this tool to create a table on the EER Diagram canvas.

Clicking the canvas, creates a table. To edit this table, right-click it and choose Edit Table or Edit in new Window from the pop-up menu. You can also simply double-click the table to load it into the table editor.

From the keyboard, use the T key to activate this tool.

For more information about creating and editing tables see Section 7.7.1.3, “The MySQL Table Editor”.

Use this tool to create a view on an EER Diagram canvas.

When this tool is activated, a schema drop-down box appears on the toolbar below the main menu, allowing you to associate the new view with a specific schema. You can also select a color for the object by choosing from the color drop down list box to the right of the schema list box.

After selecting this tool, clicking the canvas creates a new view. To edit this view, right-click it and choose Edit View or Edit in New Window ... from the pop-up menu.

From the keyboard, use the V key to activate this tool.

For more information about creating and editing views see Section 7.7.3, “Creating Views”.

Use this tool to create a routine group on the EER Diagram canvas.

When this tool is activated, a schema drop-down box appears on the toolbar below the main menu, allowing you to associate the routine group with a specific schema. You can also select a color for the routine group by choosing from the color drop down list box to the right of the schema list box.

After selecting this tool, clicking the canvas creates a new group. To edit this view, right-click it and choose Edit Routine Group or Edit in New Window ... from the pop-up menu.

From the keyboard, use the G key to activate this tool.

For more information about creating and editing routine groups see Section 7.7.4.2, “Routine Groups”.

The five relationship tools are used to represent the following relationships:

These tools appear at the bottom of the vertical tool bar. Mouse over each tool to see a text hint that describes its function.

For more information about relationships see Section 7.7.2, “Creating Foreign Key Relationships”.

Double-clicking the Add table icon in the Physical Schemata section of the MySQL Model page adds a table with the default name of table1. If a table with this name already exists, the new table is named table2.

Adding a new table automatically opens the table editor docked at the bottom of the application. Using the table editor is described in Section 7.7.1.3, “The MySQL Table Editor”.

Right-clicking a table opens a pop-up menu with the following options:

If the table editor is not open the Edit Table ... option opens it. If it is already open, the selected table replaces the previous one. Edit in New Window opens a new table editor tab.

The cut and copy options are useful for copying tables between different schemata.

Any tables added to the Physical Schemata also show up in the Catalog palette on the right side of the application. They may be added to an EER Diagram by dragging and dropping them from this palette.

Tables can also be added to an EER Diagram using the table tool on the vertical toolbar. To do this make sure that the EER Diagram tab is selected, and right-click the table icon on the vertical toolbar. The table icon is the rectangular tabular icon.

Clicking the mouse on this icon changes the mouse pointer to a table pointer. You can also change the mouse pointer to a table pointer by pressing the T key.

Choosing the table tool changes the contents of the toolbar that appears immediately below the menu bar. When the Tables pointer is active, this toolbar contains a drop down list box of schemata, a drop down list box of engines, a drop down list box of collations, and a drop down color chart. Use these list boxes to select the appropriate schema, engine, collation, and color accent for the new table. Make sure that you associate the new table with a database. The engine and collation of a table can easily be changed from the table editor and the color of your table can be changed later using the Properties palette. The Default Engine and Default Collation values refer to the database defaults.

Create a table by clicking anywhere on the EER Diagram canvas. Doing this creates a new table with the default name table1. To revert to the default mouse pointer, click the arrow icon at the top of the vertical toolbar.

Figure 7.7. A Table on an EER Diagram

As shown in the preceding diagram the primary key is indicated by a key icon and indexed fields are indicated by a different colored diamond icon. Click the arrow to the right of the table name to toggle the display of the fields. Toggle the display of indexes and triggers in the same way.

Right-clicking a table opens a pop-up menu with the following options:

With the exception of the deletion option, these menu options function as described in Section 7.7.1.1, “Adding Tables to the Physical Schemata”. The behavior of the delete option is determined by your MySQL Workbench options settings. For more information, see Section 5.4.4, “The Model Tab”.

The MySQL Table Editor is a component that enables the creation and modification of tables. Using the MySQL Table Editor you can add or modify a table's columns or indexes, change the engine, add foreign keys, or simply alter the table's name.

The MySQL Table Editor can be accessed from the MySQL Workbench by first selecting the MySQL Model tab and then double clicking a table in the Physical Schemata panel. You can also access it from an EER Diagram by double-clicking a table object.

7.7.1.3.1. The Main Editor Window

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 bottom of the application.

The MySQL Table Editor is shown in the following figure.

Figure 7.8. The Table Editor

The MySQL Table Editor consists of a work space divided into the following tabs:

• Table: Use this table to edit features that apply to the table as a whole

• Columns: Use this tab to add or modify columns

• Indexes: Use this tab to add or modify indexes

• Foreign Keys: Use this tab to add or modify foreign keys

• Triggers: Use this tab to add or modify triggers

• Partitioning: Use this tab to manage partitioning

• Options: Use this tab to add or modify various general, table and row level options

• Inserts: Use this tab for writing INSERT statements

• Privileges: Use this tab to set privileges on the table

Each of these tabs is discussed in further detail in the following sections.

7.7.1.3.3. The Columns Tab

The Columns tab is used to display and edit all the column information for a table. Using this tab, you can add, drop, and alter columns.

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

Figure 7.9. The Columns Tab

To add a column simply click the Column Name field in an empty row and enter an appropriate value. Select a data type from the Datatype drop down list box. Select a column property checkbox as required according to the following list of column properties:

• PK: Primary key

• NN: Not null

• UQ: Unique

• BIN: Binary

• UN: Unsigned

• ZF: Zero fill

• AI: Autoincrement

Right-clicking a row under the Column Name column opens a pop-up window with the following options:

• Move Up: Move the selected column up.

• Move Down: Move the selected column down.

• Delete Selected Columns: Select multiple contiguous columns by right-clicking and pressing the Shift key. Use the Ctrl 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 NULL.

• Default ": Set the column default value to ".

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 Column Comment text area. It is also possible to set the column collation, using the listbox in the Column Details panel.

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 checkbox in the Column Details section of the table editor.

If you wish to create a composite primary key you can select multiple columns and check the PK checkbox. However, there is an additional step that is required, you will need to click the Indexes tab, then in the Index Columns panel you need to set the desired order of the primary keys.

Note

When entering default values, in the case of CHAR and VARCHAR data 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.

Caution

Care must be taken when entering a default value for non-numeric ENUM columns. When entering a non-numeric default value it 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 values for ENUM columns and will fail if an attempt is made to run the script on MySQL Server.

7.7.1.3.4. The Indexes Tab

The Indexes tab holds all index information for your table. You can add, drop, and modify indexes using this tab.

Figure 7.10. The Indexes Tab

Select an index by right-clicking it. Doing this displays information about the index in the Index Columns section.

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 drop down list box. 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 ASC or DESC under the Order column. Create an index prefix by specifying a numeric value under the Length 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 and then select the Delete Selected Indexes menu option.

There are six foreign key tools on the vertical toolbar on the left side of an EER Diagram. These tools are:

An identifying relationship is one where the child table cannot be uniquely identified without its parent. Typically this occurs where an intermediary table is created to resolve a many-to-many relationship. In such cases, the primary key is usually a composite key made up of the primary keys from the two original tables. An identifying relationship is indicated by a solid line between the tables and a nonidentifying relationship is indicated by a broken line.

Create or drag and drop the tables that you wish to connect. Ensure that there is a primary key in the table that will be on the “one” side of the relationship. Click on the appropriate tool for the type of relationship you wish to create. If you are creating a one-to-many relationship, first click the table that is on the “many” side of the relationship, then on the table containing the referenced key.

Doing this creates a field in the table on the many side of the relationship. The default name of this field is table_name_key_name where the table name and the key name are both derived from the table containing the referenced key.

When the many-to-many tool is active, double-clicking a table creates an associative table with a many-to-many relationship. For this tool to function there must be a primary key defined in the initial table.

Use the Model, Menu Options menu item to set a project-specific default name for the foreign key column (see Section 7.5.1.5.4, “The Relationship Notation Menu Option”). To change the global default see Section 5.4.4, “The Model Tab”.

To edit the properties of a foreign key, double-click anywhere on the connection line that joins the two tables. Doing this opens the relationship editor.

Mousing over a relationship connector highlights the connector and the related keys as shown in the following figure.

Figure 7.11. The Relationship Connector

The film and the film_actor tables are related on the film_id field and these fields are highlighted in both tables. Since the film_id field is part of the primary key in the film_actor table, a solid line is used for the connector between the two tables.

If the placement of a connection's caption is not suitable, you can change its position by dragging it to a different location. If you have set a secondary caption, its position can also be changed. (For more information about secondary captions see Section 7.7.2.3, “The Properties of a Connection”. Where the notation style allows, Classic for instance, the cardinality indicators can also be repositioned.

The relationship notation style in Figure 7.11, “The Relationship Connector” is the default, crow's foot. If you are using a commercial version of MySQL Workbench you can change this. For more information, see Section 7.5.1.5.4, “The Relationship Notation Menu Option”.

You can select multiple connections by holding down the Ctrl key as you click a connection. This can be useful for highlighting specific relationships on an EER diagram.

Double-clicking a relationship on the EER diagram canvas opens up the relationship editor. This has two tabs: Relationship, and Foreign Key.

The Relationship tab

In the Relationship tab you can set the caption of a relationship using the Caption text box. This name displays on the canvas and is also the name used for the constraint itself. The default value for this name is fk_source_table_destination_table. Use the Model, Menu Options menu item to set a project-specific default name for foreign keys. To change the global default see Section 5.4.4, “The Model Tab”.

You can also add a secondary caption to a relationship and also a comment.

The Visibility Settings frame is used to determine how the relationship is displayed on the EER Diagram canvas. Fully Visible is the default but you can also choose to hide relationship lines or to use split lines. The split line style is pictured in the following:

Figure 7.12. The Split Connector

To set the notation of a relationship go to the Model, Relationship Notation menu item. For more information, see Section 7.5.1.5.4, “The Relationship Notation Menu Option”.

The Foreign Key tab

The Foreign Key tab contains several frames: Referencing Table, Cardinality and Referenced Table.

The Mandatory checkboxes are used to select whether the referencing table and the referenced table are mandatory. The default value for both of these constraints is true, which is indicated by the checkboxes being checked.

In the Cardinality frame there is a set of radio buttons that allow you to choose whether the relationship is one-to-one or one-to-many. There is also a checkbox that enables you to specify whether the relationship is an identifying relationship.

To select a connection, right-click it. When a connection is selected it is highlighted and its properties are displayed in the properties palette. The properties of a connection are quite different from the properties of other objects. These properties are described in the following list:

In most cases you can change the properties of a relationship using the relationship editor rather than the Properties palette.

If you make a relationship invisible by hiding it using the relationship editor's Visibility Settings, and then the relationship editor is closed, you will no longer be able to select the relationship to bring up its relationship editor. To make the relationship visible again you will need to expand the table object relating to the relationship in the Layers palette and select the relationship object. Once selected, you can edit the object by right-clicking, and selecting Edit Object. You can then set the Visibility Settings to Fully Visible. The relationship will then be visible in the EER Diagram window.

Double-clicking the Add View icon in the Physical Schemata section of the MySQL Model page adds a view with the default name of view1. If a view with this name already exists, the new view is named view2.

Adding a new view automatically opens the view editor docked at the bottom of the application. Using the view editor is described in Section 7.7.3.3, “The View Editor”.

Right-clicking a table opens a pop-up menu with the following options:

If the table editor is not open the Edit View ... option opens it. If it is already open, the selected table replaces the previous one. Edit in New Window opens a new view editor tab.

The cut and copy options are useful for copying views between different schemata and Copy SQL to Clipboard copies the CREATE VIEW statement to the clipboard.

Any views added to the Physical Schemata also show up in the Catalog palette on the left side of the application. They may be added to an EER Diagram, when in EER Diagram view, by dragging and dropping them from this palette.

Views can also be added to an EER Diagram using the View tool on the vertical toolbar. To do this make sure that the EER Diagram tab is selected, and left-click the view icon on the vertical toolbar. The view icon is the two overlapping rectangles found below the table icon.

Clicking this icon changes the mouse pointer to a view pointer. You can also change the mouse pointer to a view pointer by pressing the V key.

Choosing the View tool changes the contents of the toolbar that appears immediately below the main menu bar. When the Views pointer is active, this toolbar contains a drop down list box of schemata and a drop down color chart. Use these list boxes to select the appropriate schema and color accent for the new view. Make sure that you associate the new view with a database. The color of your view can easily be changed later using the Properties palette.

Create a view by clicking anywhere on the EER Diagram canvas. This creates a new view with the default name view1. To revert to the default mouse pointer, click the arrow icon at the top of the vertical toolbar.

Right-clicking a view opens a pop-up menu. With the exception of the delete option, these menu options function as described in Section 7.7.3.1, “Adding Views to the Physical Schemata”. The behavior of the delete option is determined by your MySQL Workbench options settings. For more information, see Section 5.4.4, “The Model Tab”.

You can invoke the view editor by double-clicking a view object on the EER Diagram canvas or by double-clicking a view in the Physical Schemata section on the MySQL Model page. Doing this opens the view editor docked at the bottom of the application. Double-clicking the title bar undocks the editor. Do the same to redock it. Any number of views may be open at the same time. Each additional view appears as a tab at the top of the view editor.

There are three tabs at the bottom of the view editor, the View, Comments, and the Privileges tabs. Navigate between different tabs using the mouse or from the keyboard by pressing Ctrl + Alt + Tab.

The View Tab

From the View tab of the view editor you can perform the following tasks:

The Comments Tab

This tab enables you to enter comments for a particular view.

The Privileges Tab

The Privileges tab of the view editor functions in exactly the same way as the Privileges tab of the table editor. For more information, see Section 7.7.1.3.10, “The Privileges Tab”.

When you select a view on the EER Diagram canvas, its properties are displayed in the Properties palette. Most of the properties accessible from the Properties palette apply to the appearance of a view on the EER Diagram canvas.

For a list of the properties accessible through the Properties palette see Section 7.5.11, “The Properties Palette”.

Layers are added to an EER Diagram using the Layer tool on the vertical toolbar. To do this select an EER Diagram tab and right-click the layer icon on the vertical toolbar. The layer icon is the rectangle with an ‘L’ in the lower left corner and it is found below the eraser icon.

Clicking the mouse on this icon changes the mouse pointer to a layer pointer. You can also change the mouse pointer to a layer pointer by pressing the L key.

Choosing the Layer tool changes the contents of the toolbar that appears immediately below the menu bar. When the Layers pointer is active, this toolbar contains a drop down color chart. Use this list box to select the color accent for the new layer. The color of your layer can easily be changed later using the Properties palette.

Create a layer by clicking anywhere on the EER Diagram canvas and, holding the left mouse button down, draw a rectangle of a suitable size. This creates a new layer with the default name layer1. To revert to the default mouse pointer, click the arrow icon at the top of the vertical toolbar.

Find below an image of a layer containing a number of views:

Figure 7.13. The Layer Object

Use the name property of the Properties palette to change the name of a layer.

Right-clicking a layer opens a pop-up menu with the following options:

The cut and copy options are useful for copying layers between different schemata.

Since layers are not schema objects, no confirmation dialog box opens when you delete a layer regardless of how you have configured MySQL Workbench. Deleting a layer does not delete schema objects from the catalog.

When you select a layer on the EER Diagram canvas, its properties are displayed in the Properties palette. The properties accessible from the Properties palette apply to the appearance of a layer on the EER Diagram canvas.

In some circumstances you may want to make a layer invisible. To do this, select the layer and, in the Properties palette, set the visible property to False. To locate an invisible object, open the Layers palette and select the object by double-clicking it. Once an object is selected you can reset the visible property from the Properties palette.

For a list of the properties accessible through the Properties palette see Section 7.5.11, “The Properties Palette”. In addition to the properties listed there, a layer also has a description property. Use this property to document the purpose of the layer.

Double-clicking the Add Note icon in the Model Notes section of the MySQL Model page adds a note with the default name of note1. If a note with this name already exists, the new note is named note2.

Adding a new note automatically opens the note editor docked at the bottom of the application. Using the note editor is described in Section 7.7.6.2, “The Note Editor”.

Right-clicking a note opens a pop-up menu with the following options:

The Edit Note ... option opens the note editor. Using the note editor is described in Section 7.7.6.2, “The Note Editor”.

The cut and copy options are useful for copying notes between different schemata.

Notes can only be added on the MySQL Model page.

You can invoke the note editor by double-clicking a note object in the Model Note section on the MySQL Model page. Doing this opens the note editor docked at the bottom of the application. Double-clicking the note tab undocks the editor. Double-click the title bar to redock it. Any number of notes may be open at the same time. Each additional note appears as a tab at the top of the note editor.

Using the editor you can change the name of a note or its contents.

Text objects can be added to an EER Diagram using the Text Object tool on the vertical toolbar. To do this make sure that the EER Diagram tab is selected, and right-click the text object icon on the vertical toolbar. The text object icon is the rectangular icon found below the label icon.

Clicking the mouse on this icon changes the mouse pointer to a text object pointer. You can also change the mouse pointer to a text object pointer by pressing the N key.

Choosing the Text Object tool changes the contents of the toolbar that appears immediately below the menu bar. When the Text Object pointer is active, this toolbar contains a drop down color chart. Use this list box to select the color accent for the new text object. The color of your text object can easily be changed later using the Properties palette.

Create a text object by clicking anywhere on the EER Diagram canvas. This creates a new text object with the default name text1. To revert to the default mouse pointer, click the arrow icon at the top of the vertical toolbar.

Right-clicking a text object opens a pop-up menu. These menu options are identical to the options for other objects. However, since a text object is not a database object, there is no confirmation dialog box when you delete a text object.

You can invoke the text object editor by double-clicking a text object on the EER Diagram canvas. Doing this opens the editor docked at the bottom of the application. Double-clicking the text object table undocks the editor. Double-click the title bar to redock it. Any number of text objects may be open at the same time. Each additional text objects appears as a tab at the top of the text editor.

Using the editor you can change the name of a text object or its contents.

Images can be added to an EER Diagram using the Image tool on the vertical toolbar. To add an image make sure that the EER Diagram tab is selected, and right-click the image icon on the vertical toolbar. The image icon is the icon just above the table icon.

Clicking the mouse on this icon changes the mouse pointer to an image pointer. You can also change the mouse pointer to an image pointer by pressing the I key.

Create a image by clicking anywhere on the EER Diagram canvas. This opens a file open dialog box. Select the desired image, and close the dialog box to create an image on the canvas. To revert to the default mouse pointer, click the arrow icon at the top of the vertical toolbar.

Right-clicking this object opens a pop-up menu with the following options:

These menu options function in exactly the same way as they do for other objects on an EER diagram. However, images are not database objects so there is no confirmation dialog box when they are deleted.

You can invoke the image editor by double-clicking a image object on an EER Diagram canvas. Doing this opens the image editor docked at the bottom of the application. Double-clicking the image editor tab undocks the editor. Double-click the title bar to redock it. Any number of images may be open at the same time. Each additional image appears as a tab at the top of the image editor,

Reverse engineering using a create script is done by using the File, Import, Reverse Engineer MySQL Create Script ... menu options. Doing this opens a file open dialog box with the default file type set to an SQL script file, a file with the extension sql.

You can create a data definition (DDL) script by executing the mysqldump db_name --no-data > script_file.sql command. Using the --no-data option ensures that the script contains DDL statements only. However, if you are working with a script that also contains DML statements you need not remove them; they will be ignored.

Use the --databases option with mysqldump if you wish to create the database as well as all its objects. If there is no CREATE DATABASE db_name statement in your script file, you must import the database objects into an existing schema or, if there is no schema, a new unnamed schema is created.

If your script creates a database, a new physical schemata tab is created on the MySQL Model page.

Any database objects may be imported from a script file in this fashion; tables, views, routines, and routine groups. Any indexes, keys, and constraints are also imported. Objects imported using an SQL script can be manipulated within MySQL Workbench in the same way that any other objects can.

Before exiting, be sure to save the schema. Choose the File, Save menu item and the reverse-engineered database will be saved as a MySQL Workbench file with the extension mwb.

See Section 7.8.1, “Importing a Data Definition SQL Script” for a tutorial on reverse engineering the sakila database.

This section explains how to reverse engineer a live database using MySQL Workbench.

Select the Database, Reverse Engineer ... menu item from the main menu. Doing this opens the Reverse Engineer Database wizard.

Figure 7.14. Reverse Engineer Database Wizard

The first page of the wizard enables you to set up a connection to the live database you wish to reverse engineer. This enables you to set up a new connection, or select a previously created connection. Typical information required for the connection includes hostname, username and password.

Once this information has been entered, or you have selected a stored connection, click the Next button to proceed to the next page:

Figure 7.15. Connect to DBMS

Review the displayed information to make sure that the connection did not generate errors, then click Next.

On the next page you can select the database schema you want to connect to among those available on the server. Simply click the checkbox or checkboxes associated with the schema you wish to process:

Figure 7.16. Select Schemata

Once you have selected the desired schema, click the Next button to continue.

The wizard then displays the tasks it carried out and summarizes the results of the operation:

Figure 7.17. Fetch Object Info

Review the results before clicking Next to continue.

The next page is the Select Objects page. It is sectioned off by object type. This screen is of special interest if you do not wish to import all the objects from the existing database—this screen gives you the option of filtering which objects are imported. Each section has a Show Filter button. Click this button if you do not want to import all the objects of a specific type.

Figure 7.18. Select Objects

For the Import MySQL Table Objects section, if you click the Show Filter button the following page is displayed:

Figure 7.19. Show Filter

This enables you to select specific tables that you wish to import. Having selected the desired tables you can optionally hide the filter by clicking the Hide Filter button.

The other sections, such as MySQL Routine Objects, have similar filters available.

You can click Execute to continue to the next page.

The wizard then displays the tasks that have been carried out and whether the operation was successful or not. If errors were generated then you can click the Show Logs button to see the nature of the errors.

Figure 7.20. Progress

Click Next to continue to the next page.

The final screen of the wizard provides a summary of the reverse engineered objects:

Figure 7.21. Results

Click Finish to exit the wizard.

Before exiting MySQL Workbench be sure to save the schema. Choose the File, Save menu item and the reverse-engineered database will be saved as a MySQL Workbench file with the extension mwb.

7.7.9.2.1. Errors During Reverse Engineering

During reverse engineering the application checks for tables and views that duplicate existing names and disallows duplicate names if necessary. If you attempt to import an object that duplicates the name of an existing object you will be notified with an error message. To see any errors that have occurred during reverse engineering you can click the button Show Logs. This will create a panel containing a list of messages, including any error messages than may have been generated. Click the Hide Logs button to close the panel.

Figure 7.22. Message Log

If you wish to import an object with the same name as an existing object, rename the existing object before reverse engineering.

If you import objects from more than one schema, there will be a tab in the Physical Schemata section of the MySQL Model page for each schema imported.

You cannot reverse engineer a live database that has the same name as an existing schema. If you wish to do this, first rename the existing schema.

To create a script of your database model use the Export option found under the File menu. You may export a script to alter an existing database or create a new database. The script to create a database is similar to the one created using the mysqldump db_name command.

If you choose to create a database, there are a number of export options that you may choose from.

7.7.10.1.1. Creating a Schema

Selecting File, Export, Forward Engineer SQL CREATE Script will start the Forward Engineer SQL Script wizard. The first page of the wizard is:

Figure 7.23. SQL Export Options

The SQL Export Options displays the following facilities:

Output SQL Script File

You can enter the name of your output file by entering it into the Output SQL Script File text box, or by using the Browse button to select a file. If this text box is left blank you will be able to view the generated script, but it will not be saved to a file.

Generate DROP Statements Before Each CREATE Statement

Omit Schema Qualifier in Object Names

Generate Separate CREATE INDEX Statements

Choosing this option creates separate statements for index creation instead of creating indexes as part of a CREATE TABLE statement.

Add SHOW WARNINGS after every DDL statement

Do Not Create Users. Only Export Privileges

To update the privileges of existing users as opposed to creating new users, select this check box. Exporting privileges for nonexistent users will result in errors when you execute the CREATE script. Exporting users that already exist, will also result in an error.

Generate INSERT Statements for Tables

If you have added any records to a table using the Insert tab of the MySQL Table Editor, choose this option. For more information about inserting records see Section 7.7.1.3.9, “The Inserts Tab”.

Clicking Next takes you to the SQL Object Export Filter page where you can select the objects you wish to export.

Figure 7.24. SQL Object Export Filter

Precise control over the objects to be exported can be fine tuned by clicking the Show Filter button. Once the objects to be exported have been selected it is possible to reduce the expanded panel by clicking the same button, now labelled Hide Filter.

Having selected the objects you wish to export you can click the Next button to review the script that has been generated:

Figure 7.25. Review Generated Script

The Finish button saves the script file and exits. You may return to the previous screen using the Back button.

You can then use the saved script to create a database.

7.7.10.1.2. Altering a Schema

The menu option for altering a schema, Forward Engineer SQL ALTER Script ..., is used for updating a database that has been redesigned within MySQL Workbench. Typically, this option is used when the SQL script of a database has been imported into MySQL Workbench and changed, and then you want to create a script that can be run against a database to alter it to reflect the adjusted model. For instructions on importing a DDL script see Section 7.7.9.1, “Reverse Engineering Using a Create Script”.

Select File, Export, Forward Engineer SQL ALTER Script to start the Foward Engineer an ALTER Script wizard. You will be presented with the first page:

Figure 7.26. Options

This first screen enables you to select a SQL script and compare it with the model currently in MySQL Workbench. The difference between the two models will be used to create an alter script that can be used to modify the target schema so that it matches the model held in MySQL Workbench. You can also simply view the script generated, rather than saving it to a file, by leaving the Output File text box empty.

Note

The script selected as the Input File must use full schema qualifiers, such as schema_name.table_name, otherwise MySQL Workbench will not be able to generate a useable alter script.

Pressing Next brings you to the Review SQL Script screen.

Figure 7.27. Script

Here you can review and change the alter script that will be generated. Make any changes you wish and, if you are happy with the changes, save the ALTER script to file using the Save to File ... button. You can also click the Execute button to write the script to the previously specified output file.

The generated script can then be used to update the database.

Use forward engineering to export your schema design to a MySQL server.

Select the schema that you wish to forward engineer and then choose Database, Forward Engineer ... option from the main menu.

The first page to be displayed is Catalog Validation (validation is only available in the Standard Edition):

Figure 7.28. Catalog Validation

Click Run Validations to validate the catalog.

Click the Next to continue.

The next page enables you to set options for the database to be created. These options are as described in Section 7.7.10.1.1, “Creating a Schema”:

Figure 7.29. Options

Select the required options and then click Next.

The next page enables you to select the objects to forward engineer:

Figure 7.30. Select Objects to Forward Engineer

To select a subset of objects to forward engineer use the Show Filter/Hide Filter button, and then select specific objects. Once you have selected your objects click Next to continue

On the Review Script page you may review and edit the SQL script that will be executed:

Figure 7.31. Review Script

Click Next to continue if you are satisfied with the generated script.

The next step of the process is to connect to a MySQL server in order to create the new database schema. This page enables you to use a previously stored connection, or enter the connection parameters:

Figure 7.32. Set parameters for connecting to a DBMS

Once the connection parameters have been set click Execute. The next page of the wizard displays the results of the forward engineering process:

Figure 7.33. Set parameters for connecting to a DBMS

You can confirm the creation of the schema by connecting to the target MySQL server and issuing the SHOW DATABASES; command.

It is possible to synchronize a model in MySQL Workbench with a live database. By default, the synchronization process will change the live database to be the same as the model, but this is configurable during the synchronization process.

MySQL Workbench enables control over the direction of synchronization, and the objects synchronized, in a completely flexible way. You can choose to synchronize only certain tables, allow synchronization to the live database only, allow synchronization from the live database to the model only, or a combination of directions. In effect you have complete control as to whether the synchronization is unidirectional or bidirectional, and which objects exactly are subject to synchronization. This is all controlled in the Select Changes to Apply page of the synchronization wizard:

Figure 7.34. Model and Database Differences

In the above example the live database consists of table1, table2 and table3. In MySQL Workbench an additional table, table4, has been created, along with a relationship between it and table3. Further, table5 exists in the live database, but not in the model. The actions that are configured to occur would result in table3 being altered (to include the relationship with table4), table4 being created and table5 being dropped, in the live database. It is possible to reconfigure this though.

The next example shows how the direction of synchronization can be changed:

Figure 7.35. Controlling Synchronization Direction

In this case the synchronization direction has been changed so that rather than the default action of table5 being dropped from the live database, it will be incorporated into the MySQL Workbench model.

For convenience the wizard provides three additional buttons to allows synchronization directions to be applied to a group of selected changes. The Update Model button causes the selected changes to only be applied to the model itself:

Figure 7.36. Update Model Button

In the above example table7 would be added to the model.

The Ignore button causes the selected changes to be ignored, no synchronization will take place for those changes:

Figure 7.37. Ignore Button

In the above example no changes would take place.

The Update Source button causes the selected changes to only update the live database:

Figure 7.38. Update Source Button

In this example table6 would be added to the live database and table7 would be dropped from the live database.

It is also possible to control individual changes by clicking the arrows. Clicking an arrow causes it to change between the three available synchronization directions:

Figure 7.39. Click arrows to change direction of synchronization

In the above example table6 will be created in the live database, and table7 will be created in the model.

This facility enables you to create a report detailing the differences between your MySQL Workbench model, and a live database or script. Select Database, Generate Catalog Diff Report from the main menu to run the Compare and Report Differences in Catalogs wizard.

Having started the wizard the first step is to specify the catalogs you wish to compare. For example, you may simply wish to compare your live database against your current MySQL Workbench model:

Figure 7.40. Catalog Sources

You then proceed through the wizard, providing connection information if accessing a live database. The wizard will then produce a catalog diff report showing the differences between the compared catalogs:

Figure 7.41. Catalog Diff Report

To create an EER diagram for the sakila database, first add an EER diagram by double-clicking the Add Diagram icon in the EER Diagrams panel. This should create and open a new EER Diagram.

The EER Diagram canvas is where object modeling takes place. To add a table to the canvas, select the Catalog tab in the middle panel on the right side of the application. This should display any schemata that appear in the MySQL Model tab.

Find the sakila schema and expand the view of its objects by clicking the + button to the left of the schema name. Expand the tables list in the same way.

You can add tables to the EER canvas by picking them up from the Catalog panel and placing them on the canvas. Drop the address table and the city table onto the canvas.

Figure 7.42. Adding Tables to the Canvas

MySQL Workbench automatically discovers that address.city_id has been defined as a foreign key referencing the city.city_id field. Drop the country table onto the canvas and immediately you should see the relationship between the country table and the city table. ( See Figure 7.45, “The sakila EER Diagram” to view a PNG file of all the relationships in the sakila database.)

Choose the Properties tab of the panel on the lower right and then click one of the tables on the canvas. This displays the properties of the table in the Properties window. While a table is selected you can use the Properties window to change a table's properties. For example, entering #FF0000 for the color value will change the color accent to red.

Changing the color of a table is a good way to identify a table quickly—something that becomes more important as the number of tables increases. Changing the color of a table is also an easy way to identify a table in the Model Navigator panel. This panel, the uppermost panel on the left side of the screen, gives a bird's eye view of the entire EER canvas.

Save your changes to a MySQL Workbench Models file (mwb) by choosing Save from the File menu or by using the keyboard command Ctrl S.

Create a new table by double-clicking the Add Table icon in the Physical Schemata panel. Doing this opens the table editor docked at the bottom of the application. If you wish, you can undock or dock this editor in exactly the same way as the schema editor window.

Use the first tab of the table editor to change the name, collation, and engine. You may also add a comment.

Add columns to the new table by selecting the Columns tab. Use the default column name or enter a new name of your choosing. Use the Tab key to move to the next column and set the column's data type.

Altering the table by adding indexes or other features is also easily done using the table editor.

Additional objects such as views or routines can be added in the same way as tables.

Any objects you have created can be found in the Catalog palette on the right. To view these schema objects select the Catalog tab in the middle palette on the right. View all the objects by clicking the + button to the left of the schema name.

Save your changes to a MySQL Workbench Models file (mwb) by choosing Save from the File menu or by using the keyboard command Ctrl S.

The tools in the vertical toolbar on the left of the EER Diagram tab are used for designing an EER diagram. Start by creating a table using the table tool. The table tool is the rectangular grid in the middle of the vertical toolbar. Mousing over it shows the message, Place a New Table (T).

Clicking this tool changes the mouse pointer to a hand with a rectangular grid. Create a table on the canvas by clicking anywhere on the EER Diagram grid.

Right-click the table and choose Edit in New Window from the pop-up menu. This opens the table editor, docked at the bottom of the application.

The table name defaults to table1. Change the name by entering invoice into the Name: text box. Notice that the name of the tab in the table editor and the name of the table on the canvas, both change to this new value.

Pressing Tab or Enter while the cursor is in the table name text box, selects the Columns tab of the table editor and creates a default column named, idinvoice.

Pressing Tab or Enter again sets the focus on the Datatype drop-down list box with INT selected. Notice that a field has been added to the table on the EER canvas.

Pressing Tab yet again and the focus shifts to adding a second column. Add a Description and a Customer_id column. When you are finished, close the table editor, by clicking the x button on the top left of the table editor.

Select the table tool again and place another table on the canvas. Name this table invoice_item. Next click the 1:n Non-Identifying Relationship tool.

First click the invoice_item table; notice that a red border indicates that this table is selected. Next click the invoice table. Doing this creates a foreign key in the invoice_item table, the table on the “many” side of the relationship. This relationship between the two tables is shown graphically in crow's foot notation.

Revert to the default mouse pointer by clicking the arrow at the top of the vertical toolbar. Click on the invoice_item table and select the Foreign keys tab.

Click the Foreign key Name text box. The referenced table should show in the Referenced Table column and the appropriate column in the Referenced Column column.

To delete the relationship between two tables, click the line joining the tables and then press Ctrl Delete.

Experiment with the other tools on the vertical toolbar. Delete a relationship by selecting the eraser tool and clicking the line joining two tables. Create a view, add a text object, or add a layer.

Save your changes to a MySQL Workbench Models file (MWB) by choosing Save from the File menu or by using the keyboard command Ctrl S.

Find below an EER digram showing all the tables in the sakila database. This image was created using the menu options File, Export, Export as PNG ....

Figure 7.45. The sakila EER Diagram

The object notation style used in Figure 7.45, “The sakila EER Diagram” is Workbench (PKs only). This notation only shows primary keys and no other columns so it is especially useful where space is at a premium. The relationship notation is the default, Crow's Foot.

As the connection lines show, each table is related to at least one other table in the database (with the exception of the film_text table). Some tables have two foreign keys that relate to the same table. For example the film table has two foreign keys that relate to the language table, namely fk_film_language_original and fk_film_language. Where there is more than one relationship between two tables, the connection lines run concurrently.

Identifying and nonidentifying relationships are indicated by solid and broken lines respectively. For example, the foreign key category_id is part of the primary key in the film_category table so its relationship to the category table is drawn with a solid line. On the other hand, in the city table, the foreign key, country_id, is not part of the primary key so the connection uses a broken line.