| title | Draw Reflexive Relationships (Visual Database Tools) | Microsoft Docs | |||
|---|---|---|---|---|
| ms.custom | ||||
| ms.date | 06/13/2017 | |||
| ms.prod | sql-server-2014 | |||
| ms.reviewer | ||||
| ms.technology | ssms | |||
| ms.topic | conceptual | |||
| helpviewer_keywords |
|
|||
| ms.assetid | e218363f-faec-46d5-9904-a537fbcc994d | |||
| author | stevestein | |||
| ms.author | sstein | |||
| manager | craigg |
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.
-
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 Query with Joins (Visual Database Tools).