Creating a Relationship Between Tables

See Also

You create a relationship between two tables when you want them to share data. You can store data in one of the tables, but make it available to both. You can also create a relationship to enforce referential integrity between related tables.

Before you create a relationship, you must first define a primary key or unique constraint in at least one of the tables. You then relate the primary key columns to matching columns in another table. Once you create the relationship, those matching columns become the foreign key of the related table. For more information, see Defining a Primary Key or Creating a Unique Constraint.

To create a relationship between tables

  1. In your database diagram, click the row selector for the database column or combination of columns that you want to relate to a column in another table.

  2. While the pointer is positioned over the row selector, drag the pointer to the related table. To relate the selected columns to the primary key of the related table, drag the pointer to the related table’s title bar.

  3. Release the mouse button. The Create Relationship dialog box appears and attempts to match the columns you selected with columns of the same name and data type in the related table.

  4. In the Create Relationship dialog box, confirm that the columns you want to relate are shown in the Primary key table and Foreign key table lists.

  5. Choose OK to create the relationship.

The primary key side of the relationship is denoted by a key symbol. In one-to-one relationships, the table that initiated the relationship determines the primary key side. For example, if you create a relationship from the pub_id column in the publishers table to the pub_id column in the pub_info table, then the publishers table is on the primary key side of the relationship.

The foreign key side of a relationship is denoted by a key symbol for a one-to-one relationship or an infinity symbol for a one-to-many relationship.

Tip   If you want to see labels for relationship lines, use the Show Relationship Labels command. For more information on relationship labels and this command, see Adding Labels to Relationship Lines.