How to: Draw Reflexive Relationships
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.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To draw a reflexive relationship
In your database diagram, click the row selector for the database column that you want to relate to another column and drag the pointer outside the table until a line appears.
Drag the line back to the selected table.
Release the mouse button. The Tables and Columns dialog box appears.
Select the foreign key column and the primary key table and column with which you want form a relationship.
Choose OK twice 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 Querying with Joins.