Map many-to-many relationships in a database diagram (ADP)

Many-to-many relationships let you relate each row in one table to many rows in another table, and vice versa. For example, you could create a many-to-many relationship between the authors table and the titles table to match each author to all of his or her books and to match each book to all of its authors. Creating a one-to-many relationship from either table would incorrectly indicate that every book can have only one author, or that every author can write only one book.

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 you want to relate. You then create a relationship from the primary key columns of each of those two tables to the matching columns in the junction table. In the Microsoft SQL Server pubs sample database, the titleauthor table is a junction table.

1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
2. In your database diagram, add the tables that you want to create a many-to-many relationship between.

   How?

   You can add a table to your database diagram to edit its structure or relate it to other tables in your diagram. You can either add existing database tables to a database diagram or insert a new table that has not yet been defined in the database. Alternatively, you can create a table or modify an existing table with the Table Designer.

   Insert a new table in a database diagram

   Adding a new table to the database diagram means that you are defining a new table that does not already exist in the database. To create a new table, you must define the individual columns that make up the table. The table is created in the database when you save the table or the database diagram.

   1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.

      To create a table in your current diagram, click New Table on the Insert menu.

      –or–

      Right-click in the diagram and select New Table.

   2. To create a table in your current diagram, click New Table on the Insert menu.

      –or–

      Right-click in the diagram and select New Table.

   3. Modify or accept the system-assigned table name, in the Choose Name dialog box, and then choose OK.
      A new table appears in the diagram in column properties view.
   4. In the first cell of the new table, type a column name. Then press the TAB key to move to the next cell.
   5. Under Data Type, select a data type for the column. Each column must have a name and data type.
      You can set the column’s other properties, such as Scale, by using the Columns property page.
   6. Repeat steps 3 and 4 for each column you want to add to the table.

   When you save your database diagram, the new table will be added to your database.

   Note   If you create a new table, then remove it from the database diagram before saving it to the database, the table name remains in memory until you close the database diagram. Also, if you delete an existing table from the database, the table name remains in memory. To use the table name again, close and restart the Database Designer.

   Add existing tables to a database diagram

   Adding an existing table means that the table you want to appear in your diagram already exists in your database. If you add a group of tables to a diagram, any relationships that exist between the tables are also added to the diagram.

   1. Right click on the database diagram, and then click Add Table .

   2. Select the table you want in the Tables list, and then click Add.

   3. Repeat step 2 for each additional table you want to add.

   4. Click Close.

   If relationships exist between the selected table and other tables in your database diagram, relationship lines are automatically drawn.

   When you add a table to a database diagram, the definition of the table (not the data that is stored in the table) is loaded from your database into memory. At that point you can edit the table’s definition. For example, you can add new columns or modify its indexes, key, relationships, or constraints.

   Add related tables to a database diagram

   For tables with existing foreign key constraints, you can easily add the related tables to the database diagram.

   1. Select one or more tables with foreign key constraints in the database diagram.
   2. Right-click on any of the selected tables and choose Add Related Tables.

   Both those tables referenced by a foreign key constraint from the selected table(s) and those referencing the selected table(s) with a foreign key constraint are added to the database diagram.

3. Create a third table by right-clicking the diagram and choosing New Table from the shortcut menu. This will become the junction table.
4. In the Choose Name dialog box, change the system-assigned table name. For example, the junction table between the titles table and the authors table is now named titleauthors.
5. Copy the primary key columns from each of the other two tables to the junction table. You can add other columns to this table, just as you can to any other table.

   How?

   You can copy columns from one table to another table in the same database diagram or in different database diagrams. Copying a column involves only the column definition. The data itself is not automatically transferred to the second table as part of this process.

   Tip

   You can use queries to copy the column's data from the original column to the new column. Use the Query Designer to run an update query to add the data to the table to which you copied the columns.

   1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
   2. In your database diagram, select the columns that you want to copy.
   3. Click the Copy button on the toolbar. This action copies the selection from the table and places the column and its current set of properties on the Clipboard.
   4. Position the cursor in the table at the location where you want to insert the columns.
   5. Click the Paste button on the toolbar. The column and its properties are inserted at the new location.

   When you copy a database column that has a user-defined data type from one database to another, the user-defined data type may not be available in the destination database. In such a case, the column will be assigned the nearest matching base data type available in that database.

6. In the junction table, set the primary key to include all the primary key columns from the other two tables.

   How?

   Define a primary key to enforce uniqueness for values entered in specified columns that do not allow nulls. If you define a primary key for a table in your database, you can relate that table to other tables, thus reducing the need for redundant data. A table can have only one primary key.

   1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
   2. In your database diagram, click the row selector for the database column you want to define as the primary key. If you want to select multiple columns, hold down the CTRL key while you click the row selectors for the other columns.
   3. Right-click the row selector for the column and select Primary Key. A primary key index, named "PK_" followed by the table name, is automatically created; you can find it on the Indexes/Keys tab of the P roperties page.

      Warning   If you want to redefine the primary key, any relationships to the existing primary key must be deleted before the new primary key can be created. A message will warn you that existing relationships will be automatically deleted as part of this process.

   A primary key column is identified by a primary key symbol in its row selector.

   If a primary key consists of more than one column, duplicate values are allowed in one column, but each combination of values from all the columns in the primary key must be unique.

   If you define a compound key, the order of columns in the primary key matches the order of columns as shown in the table in your database diagram. However, you can change the order of columns after the primary key is created. In the Column name grid, remove the columns from the primary key. Then add the columns back in the order you want. To remove a column from the key, simply remove the column name from the Column name list.

7. 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 you create.

   How?

   You create a relationship between two tables when you want to associate rows of one table with rows of another.

   Create a relationship in a database diagram

   1. In the Database window, click Database Diagrams under Objects, click the database diagram you want to open, and then click Design on the Database window toolbar.
   2. In your database diagram, click the row selector for the database column or combination of columns that you want to relate to a column in another table.
   3. While the pointer is positioned over the row selector, click and drag to the related table.
   4. Release the mouse button. The Create Relationship dialog box appears and attempts to match the columns you selected with columns of the same name and data type in the related table.
   5. In the Create Relationship dialog box, confirm that the columns you want to relate are shown in the Primary key table and Foreign key table lists.
   6. Choose OK to create the relationship.

   On the diagram, the primary key side of the relationship is denoted by a key symbol. In one-to-one relationships, the table that initiated the relationship determines the primary key side. For example, if you create a relationship from the pub_id column in the publishers table to the pub_id column in the pub_info table, then the publishers table is on the primary key side of the relationship.

   Create a relationship in Table Designer

   1. Click Tables under Objects, click the table that is on the foreign key side of the relationship, and then click Design on the Database window toolbar.
   2. Right-click in the Table Designer and choose Relationships.
   3. Click the New button.
   4. From the drop-down list in Primary key table, choose the table that will be on the primary-key side of the relationship. In the grid beneath, enter the columns contributing to the table’s primary key. In the adjacent grid cell to the left of each column, enter the corresponding foreign-key column of the foreign-key table.
      The table designer suggests a name for the relationship. To change this name, edit the contents of the Relationship name text box.
   5. Choose Close to create the relationship.

   Note   The creation of a junction table in a database diagram does not insert data from the related tables into the junction table.