Define a relationship in a Database Model diagram

Define relationships using primary and foreign keys so that you can create a collection of data from more than one table.

What do you want to do?

Create a relationship in a database model diagram

Remove a relationship from a database model

Define a relationship as one-to-one or as one-to-many

Can't find the database modeling features?

It is most likely that your edition of Microsoft Office Visio doesn't include the features you are looking for. To find out which edition of Visio you have, click About Microsoft Office Visio on the Help menu. The name of the edition is in the top line of text in the dialog box.

Microsoft Office Visio Standard does not include the Database Model Diagram template.

Microsoft Office Visio Professional supports the reverse engineering features for the Database Model Diagram template (that is, using an existing database to create a model in Visio) but it does not support forward engineering (that is, using a Visio database model to generate SQL code).

Note: You can find the full suite of database modeling features, including both reverse engineering and forward engineering, in Visio for Enterprise Architects. Visio for Enterprise Architects is included in MSDN Premium Subscription, which is available with Visual Studio Professional and Visual Studio Team System role-based editions.

Create a relationship in a database model diagram

  1. Make sure both tables are visible in the diagram. If you reverse engineered the model from an existing database, you may need to drag one or both tables from the Tables and Views window onto the drawing page.

  2. Double-click the table you want for the primary key side of the relationship.

  3. In the Database Properties window, under Categories, click Columns.

  4. In the grid, click the column you want to use to uniquely identify each row in the table and select the PK check box to set it as the primary key.

  5. From the Object Relational or Entity Relationship stencil drag a Relationship shape and drop it onto a blank space on the page.

  6. Connect the higher end to the table with the parent table.

  7. Connect the other end to the child table.

    If the second table doesn't already contain a column with the same name as the primary key, the modeler adds it to the second table as a foreign key.

Note: If relationship lines disappear, on the Database menu, point to Options, and click Document. On the Relationships tab, under Show, select the Relationships check box.

Top of Page

Remove a relationship from a database model

  1. Click the relationship in your model and press delete.

  2. In the Delete Object dialog box do one of the following:

    • Click Yes to remove the relationship from both the drawing and the underlying model.

    • Click No to remove only the line on the drawing.

      Note: If you click No and later decide to add the line back into the drawing, select one of the tables in the relationship and, on the Database menu, click Show Related Tables.

Tip: You can choose to always or never drop deleted relationships from the underlying model. On the Database menu, point to Options, click Modeling, and go to the Logical diagram tab.

Top of Page

Define a relationship as one-to-one or as one-to-many

The cardinality of a relationship describes how many rows in one table can relate to how many rows in another table. For example, one department can have many employees, but an employee can belong to only one department. Cardinality is usually expressed as one-to-one, one-to-many, and many-to-many.

  1. Double-click the relationship.

  2. In the Database Properties window, under Categories, click Miscellaneous.

  3. Under Cardinality choose the cardinality that fits the relationship best. For one-to-many relationships that would be either Zero or more or One or more. For one-to-one relationships that would be either Zero or one or Exactly one.

Top of Page

Show relationships and their properties in a diagram

You can choose to show or hide information about relationships in your diagram.

  1. On the Database menu, point to Options, and then click Document.

  2. On the Relationships tab, check information you want to show on the diagram.

    You can show or hide

    • Relationships

    • Cardinality

    • Crow's feet notation

    • Verb phrase

    • Referential actions

Top of Page

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×