Drawing a Single-Table Reflexive Relationship

You create a reflexive relationship to link a column in a table with another column 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 the foreign key of the table.

To draw a reflexive relationship

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

When you run queries against a table, a reflexive relationship is called a self-join. For information about querying tables with joins, see Querying Using Multiple Tables.