Documentation Home
MySQL Workbench Manual
Related Documentation Download this Manual
PDF (US Ltr) - 16.3Mb
PDF (A4) - 16.3Mb
HTML Download (TGZ) - 14.6Mb
HTML Download (Zip) - 14.7Mb

MySQL Workbench Manual  /  ...  /  Adding Foreign Key Relationships Using an EER Diagram Adding Foreign Key Relationships Using an EER Diagram

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

  • one-to-one non-identifying relationship

  • one-to-many non-identifying relationship

  • one-to-one identifying relationship

  • one-to-many identifying relationship

  • many-to-many identifying relationship

  • Place a Relationship Using Existing Columns

Differences include:

  • An identifying relationship: identified by a solid line between tables

    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.

  • A non-identifying relationship: identified by a broken (dashed) line between tables

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, “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.

Pausing your mouse pointer 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 pausing over a relationship for a second, a yellow box is displayed that provides additional information.

Figure 9.11 The Relationship Connector

Content is described in the surrounding text.

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, “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 Edition of MySQL Workbench. For more information, see Section, “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.

User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  Posted by Jack Barney on March 15, 2017
If you'd like for MySQL to generate the FK indices automatically for you, the following steps may be helpful when using the Workbench;
its for associative tables where a composite primary key is made up of foreign keys and you don't want to define or name separate indices for each foreign key constraint.

1. Create an associative table using the N:M selector tool, then choose the two tables to link.
2. Highlight each of the relationship lines attached to the new associative table, right-click and select "Delete", then "Keep" each column. This'll result in a table without any relationships.
3. Using the 1:N selector tool, select the associative table's first column, then the referenced column in the parent table.
4. Double-click on the associative table, in the window below, select the "Columns" tab (if not already visible), and uncheck the "PK" box for the column you just linked.
5. Using the 1:N selector tool, select the associative table's next column, then the referenced column in the parent table. This'll add the FK constraint column without adding an entry for the FK index.
6. Double-click on the associative table, in the window below, select the "Columns" tab (if not already visible), re-check the "PK" box for the first column.

Now, you have only the PRIMARY index/key list in the diagram for the associative table.

The following shows a sample of the output for an associative table linking a template and part tables. This would be the DDL run against the database when forward engineering:

DROP TABLE IF EXISTS `sample`.`template_part`;

CREATE TABLE IF NOT EXISTS `sample`.`template_part` (
PRIMARY KEY (`template_id`, `part_id`, `part_index`),
CONSTRAINT `fk_template_part_template_id`
FOREIGN KEY (`template_id`)
REFERENCES `sample`.`template` (`id`)
CONSTRAINT `fk_template_part_part_id`
FOREIGN KEY (`part_id`)
REFERENCES `sample`.`part` (`id`)

Sign Up Login You must be logged in to post a comment.