How to: Create many-to-many table relationships

June 18th, 2010

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.

To create a many-to-many relationship between tables in dbForge Studio for MySQL:

1. Create/Open a database diagram.

Create Database Diagram

2. Add the tables that you want to create a many-to-many relationship between.

Add Tables

3. Create a third table: right-click within the database diagram, and then click New Table. This will become the junction table.

Create Junction Table

4. In the Table Editor dialog box, enter a name for the table. For example, the junction table between the Authors table and the Books table is named Books_Authors.

Enter Table Name and Add Columns

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.

Add Primary Key

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

Create Relation

7. Define a one-to-many relationship between each of the two primary tables and the junction table.

Many-to-Many Relation

Note: The creation of a junction table in a database diagram does not insert data from the related tables into the junction table. You can copy rows from one table to another or within a table using an Insert From query.

Download a free 30-day trial of dbForge Studio for MySQL and try the database diagram functionality along with many other mighty features of the best MySQL GUI tool you can find!

Leave a Comment