Creating a Relationship Between Tables

  • Article

You create a relationship between two tables when you want to associate rows of one table with rows of another.  For more background about relationships, see Table Relationships.

To create a relationship in a database diagram

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.

  1. While the pointer is positioned over the row selector, click and drag to the related table.

  2. 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.

  3. 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.

  4. Choose OK to create the relationship.

On the diagram, 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.

To create a relationship in Table Designer

  1. Open the Table Designer for the table that will be on the foreign key side of the relationship. 

  2. Right-click in the Table Designer and choose Relationships.

  3. Click the New button. 

  4. From the drop-down list in Primary Key Table, choose the table that will be on the primary-key side of the relationship.  In the grid beneath, enter the columns contributing to the table's primary key.  In the adjacent grid cell to the left of each column, enter the corresponding foreign-key column of the foreign-key table.
    The table designer suggests a name for the relationship.  To change this name, edit the contents of the Relationship Name text box.

  5. Choose Close to create the relationship.

  6. For more details, see the Relationships Property Page.

See Also

Defining a Primary Key | Deleting a Relationship | Mapping Many-to-Many Relationships to a Database Diagram | Renaming a Relationship | Table Relationships