# Many-to-Many

Why two tables can't link many-to-many directly, and how an associative (junction) table between them turns it into two one-to-many relationships.

Journeyman · SQL · Database · Updated October 29, 2026

A **many-to-many** relationship cannot exist as a direct link between two tables. This is the concept that many rows in one table can be related to many rows in another. Adding this type of a `PK` to `FK` relationship is not possible.

For instance, one order could contain many products. But a product could be ordered by many customers, or even by the same customer on multiple occasions. In other words, one product can be contained in many orders. This is a **many-to-many** relationship.

![Many-to-Many relationship between the order and product tables](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/d5d04168e22c436388206f37cf05f4f7.png "Many-to-Many relationship between the order and product tables")

This direct relationship presents a challenge though, that needs to be addressed. Which table should contain the details of the relationship?

If an order can have many products, then should the product table contain the `order_id` foreign key along with all related data, such as quantity? But then each product would be limited to existing for just one order.

If a product can be ordered many times, then should the order table contain the `product_id` foreign key as well as all associated data, such as quantity? With this design, each order could then only contain a single product. A customer would need to create a new order for each product they want to purchase.

## Two One-to-Many Relationships

The solution is a third table between them, called an associative table (or junction table). It holds one row for each order and product pair, with that pair's details, such as the quantity. The **many-to-many** relationship becomes two one-to-many relationships: an order has many line items, and a product appears in many line items.

![The line_items table is the associative table](https://fsdtrainingmedia.blob.core.windows.net/public/images/2026/09/bdfeb7a10bf44270927b08de33421dfb.png "The line_items table is the associative table")

## Key terms

- [Many-to-Many Relationship](https://www.ask-codey.com/glossary/many-to-many-relationship)
- [Junction Table](https://www.ask-codey.com/glossary/junction-table)

Source: https://www.ask-codey.com/lessons/database-relationships-many-to-many