Define a relationship in a database model diagram

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

Note: The Database Model diagram is only available in certain versions of Visio. See Can't find the database modeling features? for more information.

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.

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.

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.

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

Can't find the database modeling features?

It is most likely that your edition of 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.

Visio Standard does not include the Database Model Diagram template.

Visio Professional and Premium editions support 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.

If you are using Visio Plan 2 and want to learn about how to engineer an existing database into a database model, see the topic, Reverse engineer an existing database.