Chapter 9 Database Design / Modeling

Some menu items are not available in the MySQL Workbench Community edition of this application, and are available only in MySQL Workbench Commercial. This is indicated where applicable.

9.1.1.1.2 The Edit Menu

Use the Edit menu to make changes to objects. The menu item text descriptions change to reflect the name of the selected object.

This menu has items for cutting, copying, and pasting. These actions can also be performed using the Control+X, Control+C, and Control+V key combinations. Undo a deletion using the Undo Delete 'object_name' item. The Control+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 Control+Y.

Also find a Delete 'object_name' menu item for removing the currently selected object. The keyboard command for this action is Control+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 example, 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 information about setting the default behavior when deleting from an EER Diagram, see Section 3.2.4, “Modeling Preferences”.

Warning

If the MySQL Model page is active, the selected object is deleted from the catalog and there will be no confirmation dialog box.

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 shortcuts for Edit Selected and Edit Selected in New Window are Control+E and Control+Shift+E, respectively.

The Select item has the following submenus:

• Select All (Keyboard shortcut, Control+A): Selects all the objects on the active EER diagram.

• Similar Figures (Objects of the same type): Finds objects similar to the currently selected object.

• Connected Figures: Finds all the objects connected to the currently selected object.

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

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

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

9.1.1.1.2.1 Find Dialog Window

Each MySQL Workbench window includes search functionality. The Find panel with Find & Replace enabled is shown below:

Figure 9.2 The Find Panel with Find & Replace

Find options

The Find dialogue options are described below:

• String Matching (default) or Regular Expression: Search by matching a string, or a PCRE regular expression.

• Ignore Case: A case-insensitive search. Works with both the String Matching and Regular Expression search methods. Enabled by default.

• Match Whole Words: If enabled, only whole strings are matched. For example, a search for "home" would not match "home_id". Disabled by default.

• Wrap Around: The search will wrap around to the beginning of the document, as otherwise it will only search from the cursor position to the end of the document. Enabled by default.

• And the arrows jump to the discovered search terms, and behave according to the Wrap Around option.

The MySQL Workbench Commercial edition includes an advanced Find facility for models:

Figure 9.3 The Find Window

You can search the following locations:

• Entire Model: Searches the entire model.

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

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

• Database Objects: Searches database objects only.

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

Enter the text you wish to search for in the Find Text list. 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 list 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 Control+F key combination. Use Control+G to find the next occurrence and Control+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.

9.1.1.1.2.2 Workbench Preferences

This menu item enables you to set global preferences for the MySQL Workbench application.

For further information, see Section 3.2, “Workbench Preferences”.

MySQL Workbench Community (GPL) for Windows version 6.1.4  revision 11773 build 1454
Configuration Directory: C:\Users\philip\AppData\Roaming\MySQL\Workbench
Data Directory: C:\Users\philip\Desktop\MySQL\MySQL Workbench 6.1.4 CE
Cairo Version: 1.8.8
OS: Microsoft Windows 7  Service Pack 1 (build 7601), 64-bit
CPU: 4x Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz, 8.0 GiB RAM
Active video adapter NVIDIA GeForce GT 610
Installed video RAM: 1024 MB
Current video mode: 1920 x 1080 x 4294967296 colors
Used bit depth: 32
Driver version: 9.18.13.2049
Installed display drivers: nvd3dumx.dll,nvwgf2umx.dll,nvwgf2umx.dll,nvd3dum,nvwgf2um,nvwgf2um
Current user language: English (United States)

The MySQL Workbench toolbar is located immediately below the menu bar. Click the tools in the toolbar to perform the following actions:

Other tools appear on the toolbar depending upon the context.

Use the Add new Diagram icon in the MySQL Model area to create EER diagrams. When you add an EER diagram, a new tab appears below the toolbar. Use this tab to navigate to the newly created EER diagram. For further discussion of EER Diagrams, see Section 9.1.2, “EER Diagram Editor”.

The Physical Schemata panel of the MySQL Model page shows the active schemata and the objects that they contain.

Expand and contract the Physical Schemata section by double-clicking the arrow on the left of the Physical Schemata title bar. When the Physical Schemata section is expanded, it displays all currently loaded schemata.

Each schema shows as a tab. To select a specific schema, click its tab. When MySQL Workbench is first opened, a default schema, mydb, is selected. You can start working with this schema or you can load a new MySQL Workbench Model file (models use the .mwb extension.)

There are a variety of ways to add schema to the Physical Schemata panel. You can open an MWB file, reverse engineer a MySQL create script, or, if you are using a commercial version of MySQL Workbench, you can reverse engineer a database by connecting to a MySQL server.

You can also add a new schema by clicking the + button on the top right of the Physical Schemata panel. To remove a schema, click its tab and use the - button found to the immediate left of the + button. To the left of these buttons are three buttons that control how database object icons are displayed:

The Schema Privileges panel has the following sections, used to create users for your schemata and to define roles —:

The following image displays the Schema Privileges section of the MySQL Model tab.

Figure 9.4 Roles and Privileges

9.1.1.5.1 Adding Roles

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

• Cut 'role_name': Cuts the role

• Copy 'role_name': Copies the role

• Edit Role...: Opens the role editor

• Edit in New Window...: Opens the role editor in a new editor window

• Delete 'role_name': Removes the role

• Copy SQL to Clipboard: Currently not implemented

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

All defined roles 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 page.

Figure 9.5 Role Editor

Select the role to which you wish to add objects. 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 it from the Roles section, 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.

Use the SQL Scripts panel to attach SQL scripts to the model for documentation and organizational purposes, and optionally these attachments can be included in the output script when performing forward engineering or model/schema synchronization.

If you created your project from an SQL script and plan to create an ALTER script, you may want to add the original script here, since it will be needed to create an ALTER script. For more information, see Section 9.4.1.1.2, “Altering a Schema”.

Figure 9.6 SQL Scripts Editor

Use the Model Notes panel to write project notes. Any scripts or notes added will be saved with your project.

Use the History palette to review the actions that you have taken. Left-clicking an entry opens a pop-up menu with the item, Copy History Entries to Clipboard. Choose this item to select a single entry. You can select multiple contiguous entries by pressing the Shift key and clicking the entries you wish to copy. Select noncontiguous entries by using the Control key.

Only actions that alter the MySQL model or change an EER diagram are captured by the History palette.

Docked at the top left of the application is the Model Navigator, or Bird's Eye panel. This panel provides an overview of the objects placed on an EER diagram canvas and for this reason it is most useful when an EER diagram is active. Any objects that you have placed on the canvas should be visible in the navigator.

The Model Navigator shows the total area of an EER diagram. A black rectangular outline indicates the view port onto the visible area of the canvas. To change the view port of an EER diagram, left-click this black outline and drag it to the desired location. You can zoom in on selected areas of an EER diagram by using the slider tool at the bottom of this window. The dimensions of the view port change as you zoom in and out. If the slider tool has the focus, you can also zoom using the arrow keys.

Figure 9.7 The Model Navigator: Example

The default size of the Model Navigator is two pages. Use the Model, Diagram Properties and Size page to change the size and diagram name.

Figure 9.8 The Model Navigator Palette

The Catalog Tree palette shows all the schemata that are present in the Physical Schemata section of the MySQL Model page. Expand the view of the objects contained in a specific schema by clicking the > button to the left of the schema name. This displays the following folder icons:

Expand each of these in turn by clicking the > button to the left of the folder icon.

The Catalog Tree palette is primarily used to drag and drop objects onto an EER diagram canvas.

You can toggle the sidebar on and off using the Toggle Sidebar button, which is located in the top right of the application.

This palette shows all of the layers and figures on an EER diagram. If a layer or figure is currently selected, an X appears beside the name of the object and its properties are displayed in the Properties palette. This is useful when determining the selected objects multiple objects were selected using the options under the Select menu item. For more information on this topic, see Section 9.1.1.1.2, “The Edit Menu”.

Selecting an object in the Layers palette also adjusts the view port to the area of the canvas where the object is located.

The Properties palette is used to display and edit the properties of objects on an EER diagram. It is especially useful for editing display objects such as layers and notes.

Selecting an object in the EER diagram displays its properties in the Properties palette.

All objects except connections have the following properties except as noted:

Tables have the following additional properties:

For a discussion of connection properties, see Section 9.1.4.3, “Connection Properties”.

The vertical toolbar shows on the left sidebar when an EER diagram tab is selected. The tools on this toolbar assist in creating EER diagrams.

Figure 9.9 The Vertical Toolbar

Clicking a tool changes the mouse pointer to a pointer that resembles the tool icon, indicating which tool is active. These tools can also be activated from the keyboard by pressing the key associated with the tool. Hover the mouse pointer over a toolbar icon to display a description of the tool and its shortcut key.

A more detailed description of each of these tools follows.

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. For information about using the table editor, see Section 8.1.11, “The MySQL Table Editor”.

Right-clicking a table opens a context menu with the following items:

If the table editor is not open, the Edit Table... item opens it. If it is already open, the selected table replaces the previous one. Edit in New Tab... opens an additional table editor tab.

Any tables added to the Physical Schemata section 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. Make sure that the EER Diagram tab is selected, then 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 schemata list, an engines list, a collations list, and a color chart list. Use these lists 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 be changed using the table editor. The color of your table can be changed 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. 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 9.10 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 items:

With the exception of the deletion item, these menu items function as described in Section 9.1.3.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 3.2.4, “Modeling Preferences”.

The vertical toolbar on the left side of an EER Diagram has six foreign key tools:

Differences include:

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. This creates a column in the table on the many side of the relationship. The default name of this column is table_name_key_name where the table name and the key name both refer to 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, Menu Options menu item to set a project-specific default name for the foreign key column (see Section 9.1.1.1.5.4, “The Relationship Notation Submenu”). To change the global default, see Section 3.2.4, “Modeling Preferences”.

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

Mousing over a relationship connector highlights the connector and the related keys as shown in the following figure. 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. After mousing over a relationship for a second, a yellow box is displayed that provides additional information.

Figure 9.11 The Relationship Connector

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 9.1.4.3, “Connection Properties”. Where the notation style permits, Classic for example, the cardinality indicators can also be repositioned.

The relationship notation style in Figure 9.11, “The Relationship Connector” is the default, crow's foot. You can change this if you are using a commercial version of MySQL Workbench. For more information, see Section 9.1.1.1.5.4, “The Relationship Notation Submenu”.

You can select multiple connections by holding down the Control 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 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 field. 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, Menu Options menu item to set a project-specific default name for foreign keys. To change the global default, see Section 3.2.4, “Modeling Preferences”.

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

The Visibility Settings section 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.

Figure 9.12 The Split Connector

To set the notation of a relationship use the Model menu, Relationship Notation menu item. For more information, see Section 9.1.1.1.5.4, “The Relationship Notation Submenu”.

The Foreign Key Tab

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

The Mandatory check boxes are used to select whether the referencing table and the referenced table are mandatory. By default, both of these constraints are true (indicated by the check boxes being checked).

The Cardinality section has a set of radio buttons that enable you to choose whether the relationship is one-to-one or one-to-many. There is also a check box that enables you to specify whether the relationship is an identifying relationship.

Right-click a connection to select it. When a connection is selected, it is highlighted and its properties are displayed in the properties palette. Connection properties are different from the properties of other objects. The following list describes them:

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 close the relationship editor, you will no longer be able to select the relationship to bring up its relationship editor. To make the relationship visible again, you must expand the table object relating to the relationship in the Layers palette and select the relationship object. To edit the selected object, right-click it, then select Edit Object. You can then set the Visibility Settings to Fully Visible. The relationship will then be visible in the EER Diagram window.

To add a view, double-clicking the Add View icon in the Physical Schemata section of the MySQL Model page. The default name of the view is 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. For information about using the view editor, see Section 9.1.5.3, “The View Editor”.

Right-clicking a view opens a pop-up menu with the following items:

If the table editor is not open, the Edit View... item 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 items are useful for copying views between different schemata. Copy SQL to Clipboard copies the CREATE VIEW statement to the clipboard.

Any views added to the Physical Schemata section also show up in the Catalog palette on the left side of the application. They may be added to an EER Diagram, when in the EER Diagram tab, 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. Make sure that the EER Diagram tab is selected, then 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. To change the mouse pointer to a view pointer from the keyboard, use 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 schemata list and a color chart list. Use these lists 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 be changed 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 item, these menu items function as described in Section 9.1.5.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 3.2.4, “Modeling Preferences”.

To invoke the view editor, double-click a view object on the EER Diagram canvas or double-click a view in the Physical Schemata section on the MySQL Model page. 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: View, Comments, and Privileges. Navigate between different tabs using the mouse or from the keyboard by pressing Control+Alt+Tab.

The View Tab

Use the View tab to 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 routine editor. For more information, see Section 9.1.6.1.2.2, “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 properties accessible through the Properties palette, see Section 9.1.1.12, “The Properties Palette”.

To add layers to an EER Diagram, use the Layer tool on the vertical toolbar. Select an EER Diagram tab and left-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 color chart list. Use this list to select the color accent for the new layer. The color of your layer can be changed later using the Properties palette.

Create a layer by clicking anywhere on the EER Diagram canvas and, while 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.

The following image shows a layer named "My Sakila Views" with several views:

Figure 9.13 The Layer Object

To open the layer editor, either double-click the layer or right-click the layer and choose the edit option. The available context-menu options are:

The cut and copy items 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.

Choosing "Edit" allows you to edit the layer name and layer background color, and the "Properties Editor" offers additional edit options.

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. 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. After an object is selected, you can reset the visible property from the Properties palette.

For a list of properties accessible through the Properties palette, see Section 9.1.1.12, “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. For information about using the note editor, see Section 9.1.8.2, “The Note Editor”.

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

The Edit Note... item opens the note editor. For information about using the note editor, see Section 9.1.8.2, “The Note Editor”.

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

Notes can be added only on the MySQL Model page.

To invoke the note editor, double-click a note object in the Model Note section on the MySQL Model page. 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.

Use the editor to change the name of a note or its contents.

To add text objects to an EER Diagram, use the Text Object tool on the vertical toolbar. Make sure that the EER Diagram tab is selected, then 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 color chart list. Use this list to select the color accent for the new text object. The color of your text object can 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.

To invoke the text object editor, double-click a text object on the EER Diagram canvas. 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.

Use the editor to change the name of a text object or its contents.

To add images to an EER Diagram, use the Image tool on the vertical toolbar. Make sure that the EER Diagram tab is selected, then 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 an image by clicking anywhere on the EER Diagram canvas. This opens a file open dialog box. Select the desired image, then 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 items:

These menu items 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.

To invoke the image editor, double-click an image object on an EER Diagram canvas. 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.

The printing menu items not enabled unless an EER Diagram is active. These items are available:

The following list names the general validation types and gives examples of specific violations:

The following list names the MySQL-specific validation types and gives examples of specific violations:

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: field. Changes here affect the name of the tab in the table editor and the name of the table on the canvas.

Pressing Tab or Enter while the cursor is in the table name field 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 list 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. 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 field. 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 Control+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 extension) by choosing Save from the File menu or by using the keyboard command Control+S.

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 to create and open a new EER Diagram editor.

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 to 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 dragging them from the Catalog panel dropping them onto the canvas. Drop the address table and the city table onto the canvas.

Figure 9.24 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. (To view all the relationships in the sakila database, see Figure 9.29, “The sakila Database EER Diagram”.)

Click the Properties tab of the panel on the lower left, 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.

Figure 9.25 Viewing The Properties

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 page, gives a bird's eye view of the entire EER canvas.

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

Create a new table by double-clicking the Add Table icon in the Physical Schemata panel. 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.

Figure 9.27 Model: Creating A New Table

Initially, the table name defaults to 'table1' in the table editor. The following screenshot describes the available actions:

Figure 9.28 Model: Editing Table Values

In our example above, columns were added using the Columns tab. Clicking an empty row will add a new column, and clicking an existing column starts edit mode. Click 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 possible using the table editor by clicking each tab within the table editor.

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

Objects are listed under 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 extension) by choosing Save from the File menu or by using the keyboard command Control+S.

Find following an EER diagram showing all the tables in the sakila database. This image was created using the File, Export, Export as PNG... menu item.

Figure 9.29 The sakila Database EER Diagram

The object notation style used in Figure 9.29, “The sakila Database EER Diagram” is Workbench (PKs only). This notation shows only primary keys and no other columns, which 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 more than one relationship exists 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.

To create a script of your database model, choose the Export item from 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.

Choosing to create a database yields additional options.

9.4.1.1.1 Creating a Schema

Select the File, Export, Forward Engineer SQL CREATE Script menu item to start the Forward Engineer SQL Script wizard. The following figure shows the first page of the wizard.

Figure 9.30 SQL Export Options

The SQL Export Options displays the following facilities:

• Output SQL Script File

  To specify the output file name, enter it into the Output SQL Script File field, or use the Browse button to select a file. Leave this field blank to view, but not save, the generated output.

• Generate DROP Statements Before Each CREATE Statement

  Select this option to generate a statement to drop each object before the statement that creates it. This ensures that any existing instance of each object is removed when the output is executed.

• Generate DROP SCHEMA

• Skip creation of FOREIGN KEYS

• Skip creation of FK Indexes as well

• Omit Schema Qualifier in Object Names

  Select this option to generate unqualified object names in SQL statements.

• Generate USE statements

• Generate Separate CREATE INDEX Statements

  Select this option to create separate statements for index creation instead of including index definitions in CREATE TABLE statements.

• Add SHOW WARNINGS after every DDL statement

  Select this option to add SHOW WARNINGS statements to the output. This causes display of any warnings generated when the output is executed, which can be useful for debugging.

• Do Not Create Users. Only Export Privileges

  Select this option to update the privileges of existing users, as opposed to creating new users. 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.

• Don't create view placeholder tables

• Generate INSERT Statements for Tables

  Select this option if you have added any rows to a table. For more information about inserting rows, see Section 8.1.1, “SQL Query Window”.

• Disable FK checks for inserts

• Create triggers after inserts

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

Figure 9.31 SQL Object Export Filter

Use Show Filter to fine tune (filter) the objects for export. After selecting the objects to export, click Hide Filter to hide the filter panel.

After selecting the objects to export, click Next to review the generated script.

Figure 9.32 Review Generated Script

You may return to the previous page using the Back button.

The Finish button saves the script file and exits. You can then use the saved script to create a database.

9.4.1.1.2 Altering a Schema

The menu item for creating an ALTER Script File is Database, Synchronize With Any Source. 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 executed against the database to alter it to reflect the adjusted model. For instructions on importing a DDL script, see Section 9.4.2.1, “Reverse Engineering Using a Create Script”.

Select the Database, Synchronize With Any Source menu item to start the wizard. You will be presented with the first page showing the introduction, and then the available options:

Figure 9.33 Synchronize With Any Source: Options

For additional information, see Section 9.5.1, “Database Synchronization”.

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

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

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

Figure 9.34 Set Parameters for Connecting to a DBMS

Click Next after setting the connection parameters. The next page of the wizard displays is Catalog Validation (validation is available only in the Commercial Edition).

Figure 9.35 Catalog Validation

Click Run Validations to validate the catalog.

Click Next to continue.

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

Figure 9.36 Options

Select the required options and then click Next.

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

Figure 9.37 Select Objects to Forward Engineer

To select a subset of objects to forward engineer, use the Show Filter/Hide Filter button, then select specific objects. After 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 9.38 Review Script

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

The next page of the wizard displays the results of the forward engineering process.

Figure 9.39 Forward Engineering Progress

You can confirm that the script created the schema by connecting to the target MySQL server and issuing a SHOW DATABASES statement.

To reverse engineer using a create script, choose the File, Import, Reverse Engineer MySQL Create Script... menu item for a model.

Tables, views, routines, routine groups, indexes, keys, and constraints can be imported from an SQL script file. Objects imported using an SQL script can be manipulated within MySQL Workbench the same as other objects.

Figure 9.40 Reverse Engineer SQL Script: Input

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

Click Execute to reverse engineer the SQL script, verify its results, and optionally place the objects in a new EER diagram.

Figure 9.41 Reverse Engineer SQL Script: Execution

Click Next to view a summary of the results, and then Finish to close the wizard.

Figure 9.42 Reverse Engineer SQL Script: Results

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.

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

Creating a DDL script

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 only DDL statements. 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.

To reverse engineer a live database, choose the Database, Reverse Engineer... menu item from the main menu. This opens the Reverse Engineer Database wizard.

Figure 9.43 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. You can set up a new connection or select a previously created stored connection. Typical information required for the connection includes host name, user name and password.

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

Figure 9.44 Connect to DBMS

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

The next page displays the schemata available on the server. Click the check box or check boxes for any schemata you wish to process.

Figure 9.45 Select Schemas

After you have selected the desired schemas, click the Next button to continue.

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

Figure 9.46 Retrieve Objects

Review the results before clicking Next to continue.

The next page is the Select Objects page. It has a section for each object type present in the schema (tables, views, routines, and so forth). This page is of special interest if you do not wish to import all the objects from the existing database. It 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. Here is this page with the filter open:

Figure 9.47 Select Objects

This page enables you to select specific tables for 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.

Click Execute to continue to the next page.

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

Figure 9.48 Reverse Engineer Progress

Click Next to continue to the next page.

The final page of the wizard provides a summary of the reverse engineered objects.

Figure 9.49 Results

Click Finish to exit the wizard.

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