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

To draw a reflexive relationship

1. In your database diagram, click the row selector for the database column or columns that you want to relate to another column or columns.
   
   
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 nonkey 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, you can use a reflexive relationship to create a self-join. For information about querying tables with joins, see Combining Tables.

See Also

Table Relationships