Draw a Reflexive Relationship

You create a reflexive relationship to link a column or columns in a table with another column or columns in the same table. For example, suppose the employee table has an emp_id column and a mgr_id column. Because each manager is also an employee, you relate these two columns by drawing a relationship line from the table to itself. This relationship ensures each manager ID that is added to the table matches an existing employee ID.

Before you create a relationship, you must first define a primary key or unique constraint for your table. You then relate the primary key column to a matching column. Once you create the relationship, the matching column becomes a foreign key of the table.

1. In the Database window, click Database Diagrams   under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, click the row selector for the database column or columns that you want to relate to another column or columns.
3. While the pointer is positioned over the row selector, drag the pointer outside the table until a line appears.
4. Drag the line back to the selected table.
5. Release the mouse button. The Create Relationship dialog box appears and attempts to match the primary key columns with the nonkey columns you dragged the line to.
6. Confirm that the columns you want to relate are shown in the Primary key table and Foreign key table lists.
7. Choose OK to create the relationship.

When you run queries against a table, you can use a reflexive relationship to create a self-join.