Create a many-to-many relationship between tables in a SQL Server database

In a many-to-many relationship, each row in one table can be associated with multiple rows in another table, and vice versa. For example, an employee can belong to multiple departments, and a department can include multiple employees. A one-to-many relationship cannot represent this scenario because one side of the relationship would be limited to a single related row.

In SQL Server, many-to-many relationships are implemented by using a junction table. The junction table stores references to the related rows by including the primary key columns from both tables.

This topic explains how to create a many-to-many relationship between the HumanResources.Employee and HumanResources.Department tables by using a junction table. You create a composite primary key in the junction table from the primary key columns of the related tables, and then create a one-to-many relationship from each table to the junction table.

Create a many-to-many relationship

1. Create a diagram or open an existing one.

2. Add the tables for which you want to create a many-to-many relationship.

3. Create a junction table:

3.1. Right-click the diagram and select New > Table.

3.2. In the editor that opens, enter the table name.

For example, use EmployeeDepartment as the junction table name.

3.3. Add the primary key columns from the related tables to the junction table.

Note

Each column in the junction table must have the same data type as the primary key column it references in the related table.

3.4. Optional: Add other columns to the table.

3.5. In the bottom panel, click Apply Changes.

3.6. Close the editor.

The created table appears in the diagram.

4. Create a one-to-many relationship from each related table to the junction table:

4.1. In the diagram, drag a primary key column from a related table to the corresponding column in the junction table.

4.2. In the Foreign Key Properties dialog, click Apply Changes.

4.3. Drag the primary key column from the other related table to the corresponding column in the junction table.

4.4. In the Foreign Key Properties dialog, click Apply Changes.

The relationships appear in the diagram.

Note

Creating a junction table in a database diagram doesn’t automatically populate it with data from the related tables.