Skip navigation links
MySQL Workbench :: 7 Data Modeling :: 7.5 Working with Models :: 7.5.2 Creating Foreign Key Relationships :: 7.5.2.1 Adding Foreign Key Relationships Using an EER Diagram
7.5.2.2 The Relationship Editor »
Section Navigation      [Toggle]

7.5.2.1. Adding Foreign Key Relationships Using an EER Diagram

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

  • The one-to-many non-identifying relationship tool

  • The one-to-one non-identifying relationship tool

  • The one-to-many identifying relationship tool

  • The one-to-one identifying relationship tool

  • The many-to-many relationship tool

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 on the table that is on the “many” side of the relationship and 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.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 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.5.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.1.5.4, “The Relationship Notation Menu Option”.

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