| title | Draw Reflexive Relationships | |||
|---|---|---|---|---|
| ms.custom | seo-lt-2019 | |||
| ms.date | 01/19/2017 | |||
| ms.prod | sql | |||
| ms.prod_service | sql-tools | |||
| ms.technology | ssms | |||
| ms.topic | conceptual | |||
| helpviewer_keywords |
|
|||
| ms.assetid | e218363f-faec-46d5-9904-a537fbcc994d | |||
| author | markingmyname | |||
| ms.author | maghan | |||
| ms.manager | jroth | |||
| ms.reviewer |
[!INCLUDEappliesto-ss-asdb-asdw-pdw-md]
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).