Developers 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, developers can 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 developers create a relationship, they must first define a primary key or unique constraint for their table. They then relate the primary key column to a matching column. Once they create the relationship, the matching column becomes a foreign key of the table. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to draw a reflexive relationship, they should follow the next steps:
1. In their database diagram, click the row selector for the database column that they want to relate to another column and drag the pointer outside the table until a line appears.
2. Drag the line back to the selected table.
3. Release the mouse button. The Tables and Columns dialog box appears.
4. Select the foreign key column and the primary key table and column with which they want form a relationship.
5. Choose OK twice to create the relationship.