Many-to-many relationships let developers relate each row in one table to many rows in another table, and vice versa. For example, they could create a many-to-many relationship between the singers table and the songs table to match each singer to all of his or her songs and to match each song to all of its singers. Creating a one-to-many relationship from either table would incorrectly indicate that every song can have only one singer, or that every singer can sing only one song. Many-to-many relationships between tables are accommodated in databases by means of junction tables. A junction table contains the primary key columns of the two tables developers want to relate. They then create a relationship from the primary key columns of each of those two tables to the matching columns in the junction table.When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create a many-to-many relationship between tables, they should follow the next steps:
1. In their database diagram, add the tables that they want to create a many-to-many relationship between.
2. Create a third table by right-clicking the diagram and choosing New Table from the shortcut menu. This will become the junction table.
3. In the Choose Name dialog box, change the system-assigned table name. For example, the junction table between the singers table and the songs table is named singerssongs.
4. Copy the primary key columns from each of the other two tables to the junction table. They can add other columns to this table, just as they can to any other table.
5. In the junction table, set the primary key to include all the primary key columns from the other two tables.
6. Define a one-to-many relationship between each of the two primary tables and the junction table. The junction table should be at the “many” side of both of the relationships they create.
The creation of a junction table in a database diagram does not insert data from the related tables into the junction table.